Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

     

     

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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's avatar
      ABilton
      Regular Visitor
      6 years ago

      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)