cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX - Quintile calculation / Ranking

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: DAX - Quintile calculation / Ranking

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

2 REPLIES 2
Super User IV

## Re: DAX - Quintile calculation / Ranking

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]))

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Community Support

## Re: DAX - Quintile calculation / Ranking

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

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors