Pivot Table TOTAL with Calculated Dimension

dselgo_eidex — Sat, 16 Nov 2024 06:20:45 GMT

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.

topic Pivot Table TOTAL with Calculated Dimension in App Development

Pivot Table TOTAL with Calculated Dimension