How to create dynamic percentile group measure

[redreg77]

Hi all,

I am trying to create a measure (as opposed to a calculated column) which can dynamically return a 'High', 'Medium', or 'Low' allocation based on the percentile band a score/metric falls into. I would like the middle 60% of values to return as 'Medium', the bottom 20% to return as 'Low', and the top 20% of vales to return as 'High'.

I want the measure to be flexible enough so that report users can change the group of values that the score/metric is being compared to.

Below I have provided three examples, each showing a different selection of company/year and a different comparison context. I provide commentary for each. Firstly though, I have provided the sample data which corresponds to the output in the three examples.

Data:

Readability Score	Company	Year
11.94935015	CBA	2016
11.97633083	CBA	2017
10.83721148	CBA	2018
13.71574802	CBA	2019
12.01272242	CBA	2020
11.50731335	NAB	2016
10.13704391	NAB	2017
9.398974402	NAB	2018
10.58990124	NAB	2019
7.748903218	NAB	2020
11.68387119	ANZ	2016
10.01846154	ANZ	2017
11.53740953	ANZ	2018
10.90252644	ANZ	2019
10.1452829	ANZ	2020
11.62521305	Westpac	2016
12.41574589	Westpac	2017
12.64317216	Westpac	2018
13.6355875	Westpac	2019
11.00893634	Westpac	2020

Examples of report user selections:

Example #1:

Here, the user has selected NAB as the Company, and 2017 as the year. The readability score is shown (10.14). And the user now has the option to compare that readability score using three different context/comparison groups. The user has selected to compare the score against all scores. Because this score is <= to the 20th percentile of all scores (it ranks 17 out of 20), then it is allocated a 'Low' Readability Level.

Example #2:

Here, the user has selected Westpac as the Company, and 2018 as the year. The readability score is shown (12.64). The user has selected to compare the score against Westpac's competitors for the same year. Because this score is >= 80th percentile of all scores (it ranks 1 out of 4), then it is allocated a 'High' Readability Level.

Example #3:

Here, the user has selected CBA as the Company, and 2016 as the year. The readability score is shown (11.95). The user has selected to compare the score against CBA's competitors across all years. Because this score falls inbetween the 20th and 80th percentiles (it ranks 4 out of 16), then it is allocated a 'Medium' Readability Level.

Any help would be much appreciated. Thank you!

[AlB]

@redreg77 

Glad to hear that. Do let me know if something is not clear.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[redreg77]

That is so valuable to me this solution! I can't thank you enough! I am inspired to keep going in my learning so I can fully understand your solution. But it all works perfectly 🙂

[AlB · Accepted solution]

@redreg77 

Great explanation. I'm not sure however whether example 3 belongs in Medium or High.

See the attached file for a possible solution.

1. Create an auxiliary table with the comparison options

2. Create measure:

1st output = SELECTEDVALUE(Table1[Readability Score])

3. Create measure: 

2nd output =
VAR currentScore_ = [1st output]
VAR selectedContext_ =
    SELECTEDVALUE ( ComparisonContext[Context] )
VAR selectedYear_ =
    SELECTEDVALUE ( Table1[Year] )
VAR selectedCompany_ =
    SELECTEDVALUE ( Table1[Company] )
VAR TS1_ =
    CALCULATETABLE ( Table1, ALL ( Table1[Year] ) ) // "vs Same company (All years)"
VAR TS2_ =
    CALCULATETABLE ( Table1, Table1[Year] = selectedYear_, ALL ( Table1[Company] ) ) // "vs Other companies (Same year)"
VAR TS3_ =
    EXCEPT (
        ALL ( Table1 ),
        CALCULATETABLE ( Table1, Table1[Year] <> selectedYear_ )
    ) //"vs Other companies (All years)"
VAR TS4_ =
    ALL ( Table1 ) // "vs All scores"
VAR _20PercentileValue_ =
    SWITCH (
        selectedContext_,
        "vs Same company (All years)", PERCENTILEX.EXC ( TS1_, Table1[Readability Score], 0.2 ),
        "vs Other companies (Same year)", PERCENTILEX.EXC ( TS2_, Table1[Readability Score], 0.2 ),
        "vs Other companies (All years)", PERCENTILEX.EXC ( TS3_, Table1[Readability Score], 0.2 ),
        "vs All scores", PERCENTILEX.EXC ( TS4_, Table1[Readability Score], 0.2 )
    )
VAR _80PercentileValue_ =
    SWITCH (
        selectedContext_,
        "vs Same company (All years)", PERCENTILEX.EXC ( TS1_, Table1[Readability Score], 0.8 ),
        "vs Other companies (Same year)", PERCENTILEX.EXC ( TS2_, Table1[Readability Score], 0.8 ),
        "vs Other companies (All years)", PERCENTILEX.EXC ( TS3_, Table1[Readability Score], 0.8 ),
        "vs All scores", PERCENTILEX.EXC ( TS4_, Table1[Readability Score], 0.8 )
    )
RETURN
    SWITCH (
        TRUE (),
        currentScore_ <= _20PercentileValue_, "Low",
        currentScore_ <= _80PercentileValue_, "Medium",
        "High"
    )

Note it is rather verbose but not complex conceptually. I've used PERCENTILEX. EXC. You might want to modify that to adapt it exactly to your needs. Or use RANKX instead. I've also included two measures 

[20_and_80_percentileValues] and [Num rows in comparison] 

to help examine the results

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @redreg77 

1. Where does the 11.01 on the 1st output card come from?

2. Can you explain with an example based on the sample datad how exactly you get to 1st and, especially, second output?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[redreg77]

Hi @AlB 
Thanks for responding to my post and pointing out the data and the report output didn't match. I have now updated my original post. Could you please take a look and see if you can help me?

Many thanks!