topic Re: Change expression based on pivot table dimension in App Development

Change expression based on pivot table dimension

dselgo_eidex — Sat, 16 Nov 2024 06:25:13 GMT

Hello. I am currently trying to put together a complex pivot table and I am running into a snag. Below is an example of what I am working with:

I have a pivot table with District, Building, Teacher, and Student as dimensions. I want to show the average of a field at each dimension level as long as there are at least 20 students in the set, otherwise show '<20':

=if(count(DISTINCT [Students]) < 20, '<20', avg([Measure Field]))

I want this rule to apply to all dimension levels EXCEPT for student. Because when you expand down to the student level, the count will always be 1 and the measure will always show '<20'.

I want to know if there is a way that I can set my expression up to know which pivot table dimension it is currently calculating over. That way I could ignore the above rule when at the student level.

Re: Change expression based on pivot table dimension

Anil_Babu_Samineni — Wed, 06 Mar 2019 06:46:12 GMT

May be try this way?

=Sum(Aggr(if(count(DISTINCT [Students]) < 20, '<20', avg([Measure Field])),Building, Teacher))

Re: Change expression based on pivot table dimension

dselgo_eidex — Wed, 06 Mar 2019 17:36:38 GMT

The aggr didn't quite work, but your suggestion gave me a great idea. I can add the TOTAL qualifier and include every dimension except [Student].

=if(count(DISTINCT TOTAL<[Districts], [Buildings], [Teachers]> [Students]) < 20, '<20', avg([Measure Field]))

I think that was what you were going for with the aggr suggestion, but for my purposes this is a little cleaner. It still has an issue when there are fewer than 20 students at the teacher level, but for what I need it works just fine.

While this solves my specific issue, it would be interesting to hear if there is a way to accomplish what I was initially asking. If there is a way to determine what dimension the expression is calculating over within the expression, but I'm pretty sure the answer is there is not.

Re: Change expression based on pivot table dimension

dselgo_eidex — Wed, 06 Mar 2019 22:16:31 GMT

After this I actually found a better way that is closer to what I was trying to do:

=if(IsNull(only([Students])),
    if(count(DISTINCT [Students]) < 20, 
        '<20', 
        avg([Measure Field])),
    avg([Measure Field])
)

Basically, it will only perform the '<20' part of the expression when only([Students]) is null (a.k.a. when there are more than one student), otherwise it calculates normally when at the student level.