Pivot Table TOTAL with Calculated Dimension

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

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.

Re: Pivot Table TOTAL with Calculated Dimension

dselgo_eidex — Wed, 13 Mar 2019 21:42:45 GMT

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

topic Re: Pivot Table TOTAL with Calculated Dimension in App Development

Pivot Table TOTAL with Calculated Dimension

Re: Pivot Table TOTAL with Calculated Dimension