Forum Discussion
Complex Formula Cube
Hi Anonymous,
In this scenario, you should format the 'Program' table as below.
The [Index] column here is necessary which is added in Query Editor mode. The [next level] here is a calculated column.
Then, you should crossjoin this table with actual data table (the first image in your original post).
In crossjoin table ('new table_1'), create three calculated column to determine the level of NPS, Retention, Satisfaction for each person. Then, create a new calculated table ('new table_2') to convert it structure which works like "Unpivot".
At last, please use a Matrix visual to host data. Please remember to drill down to the last level and turn off the "Stepped layout"
I have uploaded the .pbix file, please review it for detailed steps.
Best regards,
Yuliana Gu
v-yulgu-msft Here is another screenshot I've accidentally left out:
So there are 3 measures: NPS, Retention, Satisfaction. I already have formulas built that calculates these for each person. So using the above chart, the money they would earn is based upon:
1. The tier they are in (Minimum accounts managed)
2. Whether their stats equate to gold, silver, or bronze.
So going back to these screenshots for an example. I will focus on Timothy Witten:
So Tim manages 56 active clients. This places him in Tier 2. Based on Tier 2 and his stats, these would be his earnings:
1. Retenion is 91.23 placing him in the Retention Bronze category, so he earns $210 for this stat.
2. NPS is -4 placing him below the minimum target goal so he earns $0 for this stat.
3. Satisfaction is 91.75 placing him in the Satisfaction Silver category so he earns $130 for this stat.
Adding everything up would give him a combined total of $340 earned. Let me know if you need anything furhter explained...
Hi Anonymous,
In this scenario, you should format the 'Program' table as below.
The [Index] column here is necessary which is added in Query Editor mode. The [next level] here is a calculated column.
Then, you should crossjoin this table with actual data table (the first image in your original post).
In crossjoin table ('new table_1'), create three calculated column to determine the level of NPS, Retention, Satisfaction for each person. Then, create a new calculated table ('new table_2') to convert it structure which works like "Unpivot".
At last, please use a Matrix visual to host data. Please remember to drill down to the last level and turn off the "Stepped layout"
I have uploaded the .pbix file, please review it for detailed steps.
Best regards,
Yuliana Gu
v-yulgu-msft Thank you so much for this! I have other pieces to finish up first but I will let you know how things goes once I'm finished building this. Can't thank you enough.