cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Saket_Casper
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.

NameRegionSalesYear
Sales man 1North163596132017
Sales man 2North143351482017
Sales man 3North190545092017
Sales man 4North336539012017
Sales man 5North98865252017
Sales man 6Excluded95850972017
Sales man 7North183193892017
Sales man 8North69732262017
Sales man 9Overall2.38E+092017
Sales man 10North174464132017
Sales man 12North230056522018
Sales man 13Central881651632018
Sales man 14North327029762018
Sales man 15North114531152018
Sales man 16Central2.28E+082018
Sales man 17Central938425862018
Sales man 18Excluded1.57E+082018
Sales man 19North400228932018
Sales man 20North125599802018
Sales man 21North77255182018
Sales man 22North174450482019
Sales man 23Central3.73E+082019
Sales man 24North170926332019
Sales man 25Excluded11307282019
Sales man 26North151701692019
Sales man 27North168711962019
Sales man 28North134108972019
Sales man 29North301796402019
Sales man 30Excluded443158512019
Sales man 31Central1.62E+082019

 

Thanks,

Saket

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
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:

61.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

 

View solution in original post

2 REPLIES 2
Super User IV
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]))





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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
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:

61.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

 

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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