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
Hi Anonymous,
Please provide more description about your required output. Which part is that you were trying to calculate? What is the relationship between Tables&Logic and end result table? How to convert?
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.