How do I calculate Decile in Power BI

[Anonymous]

Hello All,

I'm looking to calculate decile (i.e. splitting up a set of ranked data into 10 equally large subsections. Similar to Percentile) for each unique combination of Area/ Tasktype/Subtype in the table listed below .

Decile because ultimately I need to be able to identify Top and Bottom 10% from the data and derive insights like:

• 20% of all data values lie below 77%
• 30% of all data values lie below 65%.
• 40% of all data values lie below 120%, etc.

@barritown 

[barritown]

Hi @Anonymous,

Since you've mentioned me here and no one has yet responded here, I can try to help. But since I am not really fluent with percentiles and deciles, I don't quite understand your task.

Do you need to split the interval [0, 150%] into 10 equal sub-intervals and then mark to which sub-interval belongs each Accuracy value? 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous]

That's correct @barritown !Thanks for replying, I'm trying to create 10 equal buckets and marking which bucket each unique combination of Area/ Tasktype/Subtype belongs to.

[barritown]

Hi @Anonymous,

The solution below is not accurate due to the way RANKX works with the duplicates, but it should work if you don't have any.

Here it is:

1. Add a calculated column named "Rank":

Rank = RANKX ( ALL ( data ), [Accuracy], data[Accuracy], ASC )

2. Add one more calculated column named "Decile":

Decile = 
VAR cnt = COUNTX ( ALL ( data ), [Accuracy] )
VAR res = SWITCH ( TRUE (),
                   [Rank] <= ( cnt + 1 ) * 1 / 10, "D1", 
                   [Rank] <= ( cnt + 1 ) * 2 / 10, "D2", 
                   [Rank] <= ( cnt + 1 ) * 3 / 10, "D3", 
                   [Rank] <= ( cnt + 1 ) * 4 / 10, "D4", 
                   [Rank] <= ( cnt + 1 ) * 5 / 10, "D5", 
                   [Rank] <= ( cnt + 1 ) * 6 / 10, "D6", 
                   [Rank] <= ( cnt + 1 ) * 7 / 10, "D7", 
                   [Rank] <= ( cnt + 1 ) * 8 / 10, "D8", 
                   [Rank] <= ( cnt + 1 ) * 9 / 10, "D9",
                   "D10" ) 
RETURN res

You will get something like this:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian