Jump to content

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


David Pesante 5

Recommended Posts

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

Link to comment
Share on other sites

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)))

 

 

 

desmearing_0.png

 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...