Formula Help

Anonymous · ‎06-15-2018

Using survey data, I've created 3 measures that counts the number of different category types all relating to feedback:

From here, I then made a formula that is called a Net Promoter Score. It is the % of promoters - the % of detractors. This formula is a calculated column:

From here, I then made another formula that calculates this number on a trailing 12 month average:

This formula I then apply to a table associated with managers so I can see the average by each person. I've also created a formula that calculates this based on the previous quarter as well:

So then from here, depending on an assigned tier and their previous quarter NPS, they are supposed to be awarded a bonus:

So at this point, everything has worked as intended.

I was thrown a curve ball and asked rather than seeing what each individual person's previous quarter NPS, if it could be displayed as a whole group. So on each row, it would be showing the overall previous quater NPS which is -17.39. This would put everybody at "Below Goal" and nobody would be awarded any monetary bonus. However, when trying to change my original [Last Quarter NPS] measure to this to have it display the same for everyone:

It messes up by [NPS Bonus] formula above. Even though everybody has an [NPS Level] of "Below Goal" certain people are still being awarded a monetary bonus when nobody should be. Specifically based upon their [Tier] and [NPS Level], the people being awarded are being awarded for the "NPS Bronze" [NPS Level] category. Does anyone know how to fix this?