Calculate the productivity percentile from published benchmarks

[StaticElectric]

Hello

I'm trying to compare the productivity of my staff with national benchmarks. I've attached the Excel version and my Power BI model attempt (it's incomplete). My version of Excel works great, but I'm having a hard time figuring out how to migrate that formula to Power BI. Published data has four levels (25th %tile, Median, 75th %tile and 90th %tile). The calculator is used to compare the production of my staff and provides additional %tiles other than published values such as (30o, 44o, 65o, 95o, etc.). The calculation steps used in Excel are below; the formula uses quite a few nested ifs:

• There are two worksheets in the attached Excel file: "%tile Calculator" and "Productivity Table".
• %tile Worksheet Calculator has user input plus desired result
• I enter the FTE, Specialty and Production input in columns C, D and E (any light blue value is a user-specified input; feel free to change these values for testing)
• The percentile is calculated in column F
• To calculate the percentile, I use VLOOKUP or index/match to retrieve the published values from the "Productivity Table" worksheet (values in red in columns H, I, J and K labeled 25%, 50%, 75%, 90%)
• The values in step 5 are adjusted for FTE in column C to account for less than 1.0 FTE workers
• Note in the steps below that every time I assign a value of %tile as 25 th or 50th I mean the published values. The actual calculation in column F is a bit long, but not complicated as below screenshot shown:
  1. If the production input is less than 25 % corresponding tile, take the production input and divide it by 25% multiplied by 0.25
  2. If the production input is greater than or equal to 25% of tile and the production input is less than the median (50o %tesela), take the difference between the production input and 25% of the tile divided between 50% of the tile multiplied by 0.25+0.25
  3. If the published entry is greater than or equal to 50th %tile, and the production entry is less than 75th %tile, then take the difference between the production input and the 50th %tile, divide it over the difference between 75th %tile and 50th %tile by 0.25 + 0.50
  4. If the production input is greater than or equal to 75o %tile and the production input is less than 90o %tesela, then take the difference between the production input and 75o % tile divided over the difference between 90o %tile to 75o % til by 0.15 +0.75
  5. Finally, if the production input is greater than or equal to 75% of tile, take the %production tile and divide it into 90% of tile by 0.90
  6. Else 0
• As you may have noticed, the first three levels differ between 25 (25 -->50 -->75) while the difference between the last two levels is 15 (75-->90)

I've been stuck on this issue for some time and as a stop gap you've been using Excel for calculation and then importing the results into Power BI. I've done a lot of research and, unfortunately, I've been more confused. From what I understand, however, that Power BI measures can be greatly simplified, as there are a lot of redundant values in my approach (which doesn't work anyway).

Thank you for your time. I've tried to explain the logic behind the formula. My hope is that it will help convey what I'm trying to achieve. The links are as follows:

Excel: https://www.dropbox.com/s/d1k7161yh7u3vb3/%25tile%20Calculator.xlsx?dl=0

Power BI: https://www.dropbox.com/s/8nnl6tubw6abdmi/Productivity%20Percentile%20Calculator.pbix?dl=0

[Icey · Accepted solution]

Hi @StaticElectric ,

Try to create a calculated column like so:

Percentile Calculator =
VAR ile25 =
    RELATED ( 'dim Published Percentile Table'[25th %ile] )
VAR ile50 =
    RELATED ( 'dim Published Percentile Table'[50th %ile] )
VAR ile75 =
    RELATED ( 'dim Published Percentile Table'[75th %ile] )
VAR ile90 =
    RELATED ( 'dim Published Percentile Table'[90th %ile] )
RETURN
    IF (
        'f Production Data'[Input] < ile25,
        DIVIDE ( 'f Production Data'[Input], ile25 ) * 0.25,
        IF (
            'f Production Data'[Input] >= ile25
                && 'f Production Data'[Input] < ile50,
            DIVIDE ( 'f Production Data'[Input] - ile25, ile50 - ile25 ) * 0.25 + 0.25,
            IF (
                'f Production Data'[Input] >= ile50
                    && 'f Production Data'[Input] < ile75,
                DIVIDE ( 'f Production Data'[Input] - ile50, ile75 - ile50 ) * 0.25 + 0.5,
                IF (
                    'f Production Data'[Input] >= ile75
                        && 'f Production Data'[Input] < ile90,
                    DIVIDE ( 'f Production Data'[Input] - ile75, ile90 - ile75 ) * 0.15 + 0.75,
                    IF (
                        'f Production Data'[Input] >= ile75,
                        DIVIDE ( 'f Production Data'[Input], ile90 ) * 0.9,
                        0
                    )
                )
            )
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StaticElectric]

@Icey  thank you very much for your help! I have done extensive testing on this for the past two days and this solution works very well! I had read about variables before but had not used them. Thank you for teaching me a new approach.  I was able to apply this solution to four other situations. I am truly grateful!