Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am currently working on creating a pivot table that uses a calculated dimension to form test percentile groups, and another dimension to show the students within those groups. The problem is that I want one of my columns on the pivot table to be the average score for the entire percentile group.
So basically, I want the table to show this when not expanded:
And then show this when I expand the percentile group (note: I hard-coded the expression to 89.7 to show an example):
Dimensions:
Percentile Group:
=replace(replace(replace( class(aggr({$}
((( count({$} DISTINCT TOTAL [Student]) - rank(TOTAL avg({$} [Score]), 3 ) ) / count({$} DISTINCT TOTAL [Student] )) * 100), [Student] ), 20), '0 <= x < ', '1st - '), '0', '0th Percentiles'), '10th Percentiles0th Percentiles', '99th Percentiles' )
Student: [Student]
Measures:
Average Score: =avg({$} [Score])
Normally, I would achieve the result I want by making the measure =avg({$} TOTAL<[Dimension]> [Score]), but since I have a calculated dimension, I am unable to do this.
Got it working with the following expression:
=Top(RangeAvg(Below(avg({$} [Score]), 0, NoOfRows())))
I had been trying expressions similar to the one above for about 6 hours before I posted this question, and then I figured it out 15 minutes later
Got it working with the following expression:
=Top(RangeAvg(Below(avg({$} [Score]), 0, NoOfRows())))
I had been trying expressions similar to the one above for about 6 hours before I posted this question, and then I figured it out 15 minutes later