Complex Formula Cube

[Anonymous]

I'm looking to build some kind of formula/cube that measures different employee statistics and calculates an end result of money earned. So far I've figured out the tier part but not sure how to start the next steps. Rather than a long explanation, I will show some screenshots of what I have and what I'm aiming to turn it into:

Tables and Logic:

What the end result should look like:

So I've figured out the tier piece but from there the rest is beyond what I know how to do in Power BI. I'm sure it is a combination of switch/if statements but I'm not sure where to begin. Can anyone help get me started? Once I see what the pattern is (hopefully there is one) I'll be able to pick up on the rest. Thanks!

[v-yulgu-msft]

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

[Anonymous]

@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...

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

@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.