Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am currently working on a complicated Pivot Table. Simply stated, I am trying to group students up into different testing percentile groups (1st - 20th percentile, 21st - 30th percentile, etc.). I've done this easily enough by creating a calculated dimension that uses the class() function to calculate the percentiles and then group them up.
The issue is that I have a second dimension (Student) where I am trying to show the Average Score of the percentile group. Normally, this would be easy and I would just use the TOTAL qualifier. But since I am using a calculated dimension, I can't get the expression to calculate over the percentile groups.
Here is what I have with no rows expanded:
And when I expand a row I expect to see this (I have hard coded the expression to 89.7 as 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 for the measure I would just use avg({$} TOTAL<[dimension 1]> [Score]). But since that dimension is a calculated dimension, I am unable to.