Looking for Expression for doing Sum of Averages. Something like this: Sum(Average Score for each Client)

[David Pesante 5]

My data basically looks like this:

 

 

 

Client Name

Score

 

 

ABC

5

 

 

ABC

5

 

 

ABC

5

 

 

ABC

5

 

 

ABC

5

 

 

ABC

5

 

 

XYZ

10

 

 

XYZ

 

10

 

 

 

 

I want to be able to calculate the Average of the averages. In this case: ABC avg = 5, XYZ average = 10. the Average of Averages = 7.5 but when this is done in spotfire (e.g. single bar chart or KPI Card) ... it shows the average accross the entire list Sum of Score / Count of Rows which yields 6.25 in this case. I need for the solution to be dynamic (filter sensitive). Calculated column will not do...

Any thoughts anyone

[Khushboo Rabadia]

Expression like below would be helpful in your case:

Sum(case when DenseRank(RowId(),[Client Name])=1 then [score] end) / UniqueCount([Client Name])where rank based on row ids for each client name and only first row value would be considered for summing the score values

[Anders Gavare]

It feels like that expression (the Sum(case when...))/UniqueCount) would only check the first row for each Client name.

 

Try for example with this data:

 

Client Name;Score
ABC;5
ABC;5
ABC;150
ABC;5
ABC;5
ABC;5
XYZ;10
XYZ;10

 

 

 

The row with 150 is completely ignored.

 

Perhaps nested aggregation with Avg of Avg (and desmearing on the inner Avg using an if multiplier) could work instead It gives (I think) reasonable result for the data above at least.

 

Avg(Avg([score]) OVER (Intersect([Client Name])) * (If(First(RowId()) OVER (Intersect([Client Name]))=RowId(),1)))

 

 

 

 

 

[Khushboo Rabadia]

Thanks agavare !!

[David Pesante 5]

Really creative solution... basically... calculate the average then multiply it by 1 only for the first occurance of that client dataset....works great! thanks!