DAX - Quintile calculation / Ranking

[Anonymous]

Hi there!

I am trying to build a DAX so that it groups salesmen into 5 groups (Quintiles) for each year based on the sales amount column. 

Here is the sample data set.

Name	Region	Sales	Year
Sales man 1	North	16359613	2017
Sales man 2	North	14335148	2017
Sales man 3	North	19054509	2017
Sales man 4	North	33653901	2017
Sales man 5	North	9886525	2017
Sales man 6	Excluded	9585097	2017
Sales man 7	North	18319389	2017
Sales man 8	North	6973226	2017
Sales man 9	Overall	2.38E+09	2017
Sales man 10	North	17446413	2017
Sales man 12	North	23005652	2018
Sales man 13	Central	88165163	2018
Sales man 14	North	32702976	2018
Sales man 15	North	11453115	2018
Sales man 16	Central	2.28E+08	2018
Sales man 17	Central	93842586	2018
Sales man 18	Excluded	1.57E+08	2018
Sales man 19	North	40022893	2018
Sales man 20	North	12559980	2018
Sales man 21	North	7725518	2018
Sales man 22	North	17445048	2019
Sales man 23	Central	3.73E+08	2019
Sales man 24	North	17092633	2019
Sales man 25	Excluded	1130728	2019
Sales man 26	North	15170169	2019
Sales man 27	North	16871196	2019
Sales man 28	North	13410897	2019
Sales man 29	North	30179640	2019
Sales man 30	Excluded	44315851	2019
Sales man 31	Central	1.62E+08	2019

Thanks,

Saket

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i define a group policy and create a calculated column:

Group =
SWITCH (
    TRUE,
    'Table'[Sales] > 20000000, "Group1",
    'Table'[Sales] > 15000000
        && 'Table'[Sales] <= 20000000, "Group2",
    'Table'[Sales] > 10000000
        && 'Table'[Sales] <= 15000000, "Group3",
    'Table'[Sales] > 5000000
        && 'Table'[Sales] <= 10000000, "Group4",
    'Table'[Sales] <= 5000000, "Group5"
)

Choose this column as a sclier, when select one, the result shows salesmen name for each year based on sales amount:

Hope this helps.

Best Regards,

Giotto Zhi

[ABilton]

If you want to use the data as a slicer, you can create a calculated column using the following formula:

Calculated Column Quintile =

// Step 1: Calculate the Maximum value within the source column

VAR vMaxValue = MAXA([SOURCE FIELD]) // e.g. 1000 is the max value

// Step 2: Set how many 'Bins' you want to divide your data into (4 = quartiles, 5 = quintiles, 10 = deciles, etc.)

VAR vSplitInto = 5

// Step 3:  Calculate the 'Bin' size

VAR vBinSize = vMaxValue / vSplitInto // e.g. 1000 / 5 = 200

// Step 4: Calculate which 'Bin' the current value is in and round it up to a whole number

RETURN ROUNDUP([SOURCE FIELD]/vBinSize,0)

[amitchandak]

I have not tried but can you check like

https://community.powerbi.com/t5/Desktop/How-do-you-slice-data-into-quartiles-and-quintiles/td-p/157...

calculate(PERCENTILE.INC(PUB2015[Revenue], 0.25),values(table[year]))