Calculating average based on different groupings

[Prateek Rawat]

Hi All,

I am calculating averages and populating line chart in the dashboard using below calculation:

[sum Total Cost grp_Quarter] / [unique Sys Ids_Quarter]

 

WhereSum Total Cost grp_Quarter is:

Sum([Cost]) over ([family_name],[Country],[Age],[Quarter],[Year]))

and

Unique Sys Ids_Quarter is:

UniqueCount([system Id]) over ([family_name],[Country],[Age]))

The line charts populates data based on the marking selection in the previous tab i.e the system ids which are selected in the previous tab are used to display averages in the line chart of the current tab.

Requirement is:

When there are two or more systems selected in the previous tab then verify if they are from same grouping or different grouping.

Grouping is "over ([family_name],[Country],[Age]))"

If it is from same grouping, the average calculation would be

Numerator =(Total cost of system 1+ Total cost of System 2 etc+.....)

Denominator = Total systems in that group.

If the systems are from different groups, the average calculation would be

For Example:

System 1:Africa, MR, P1 ; Total Systems in this Group is 100

System2: USCAN, CT, P2 ; Total Systems in this Group is 20

System3: EGM, LCS, P3; Total Systemsin this Group is 45

Numerator =(Total cost of system 1 group+ Total cost of System 2 group + Total cost of System 3 group)

Denominator = (Total Systems in Group 1+Total Systemsin Group 2+Total Systems in Group 3) -> (100+20+45)

How can Isegregate these values if they are from different groupings based on the markings in the previous tab.

[Khushboo Rabadia]

Can you please provide the sample dxp and showing the screenshots of the desired output.