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. Name Region Sales...
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

     

     

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)