Apply Formula on Group Level Instead of Individual Level

[Anonymous]

I have this formula built out:

In a nutshell, this formula is simply looking at a person's [Tier] and [NPS Level] to determine the amount of bonus they receive, if any.

I then have my data in this table:

 So as you can see, this is calculated individually for each person. A person only recieves a bonus if criteria in the above formula is met.

What I've been asked to do from here is instead of having this apply individually to each person, have it calculate for the entire group as a whole. So since the overall [Last Quarter NPS] is -17.39, this would be considered "Below Goal" for the group as a whole and nobody would receive a bonus.

One thing I've tried to implement is the ALL function from this:

To this:

But this doesn't quite work like I'd want it to since the ALL function will ignore other filters that are set. So at plain view I get this which appears correct:

But I have a page filter that allows the user to toggle back and forth between active and non active employees and when I switch to "active" only, I get these results:

So even though the status for the entire group becomes "Below Goal", people are still being rewarded a bonus when they shouldn't be. How can I fix this? It looks like what is happening is the ALL function no matter what is looking at the overal [Last Quarter NPS] group score of 4.81 which would be considered "NPS Bronze", so people are still getting rewarded that amount based on their [Tier] and [NPS Level].

[v-juanli-msft]

Hi @Anonymous

To achieve

“So since the overall [Last Quarter NPS] is -17.39, this would be considered "Below Goal" for the group as a whole and nobody would receive a bonus”

You could use the following measure.

IF(SUMX(ALL(‘ALL Survey Data’), [Last Quarter NPS])<0,BLANK(),[NPS Bonus])

Best Regards

Maggie