Announcements
cancel
Showing results for
Did you mean:
Partner - Creator III

## Pivot Table TOTAL with Calculated Dimension

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.

Labels (3)

• ### Total

1 Solution

Accepted Solutions
Partner - Creator III
Author

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

`=Top(RangeAvg(Below(avg({\$} [Score]), 0, NoOfRows())))`