Slab / Tiered Calculations using DAX

Slabs or tiered calculations are a pretty common while working with income taxes or commission rates but carrying them out using DAX can be tricky. In this post I am going to share with you one such example (with tweaks and variations) of how can you do slab or tried calculations in Power BI

 

Take a look at this data

Transaction Level Sales Data by Salesman

And we have Commission Rate Table

• The commission is paid based on Monthly Sales achieved by each Salesman
• The slab is progression based. e.g. If the Sales of Ron are $900 for Jan
  • He’ll get no commission for $100
  • He’ll get 10% of $400 (based on second slab) = $40
  • He’ll get 15% of next $400 (based on second slab) = $60
  • His total commission = $100 ($40+ $60)
• If you work with these calculations you get the idea!

Let’s see if we can create a robust DAX measure for calculating slab wise commission.

 

Relationships and a few transformations

All tables loaded in Power BI, this is how the Relationships are setup between the Sales Data and Calendar (date table)

 

I also did some minor transformations in Comm Table. This is how it looks. Notice the upper and lower values are over-lapping (that’s done intentionally)

 

I also created a simple measure for Total Sales

Total Sales = SUM(SalesData[Sales])

 

Creating a Summarized Table

1. If you noticed, we have transaction level sales data but the commission calculation will be done based on monthly sales achieved by each salesman.
2. Even if we have only Year displayed (in a pivot) our measure should still calculate based on Monthly Sales for each Salesman.

Keeping the above in mind I first create summarized table – that’ll first summarize transactional data by Year, Month and Sales Rep. Consider this half cooked measure

Commission = 
VAR MonthlySalesTable =     
    ADDCOLUMNS(
        SUMMARIZE(
            SalesData,
            'Calendar'[Year],
            'Calendar'[Month],
            SalesData[Salesman]
        ),
        'Monthly Sales',
        [Total Sales]
    )
RETURN
    COUNTROWS(MonthlySalesTable)

I have also added a column using ADDCOLUMNS which calculates Total Sales for each row in the summarized table. As of now the measure only gives the count of the summarized rows.

Quick check..

1. The max row count for any year can be 144 rows (12 months x 12 unique Salesmen)
2. But it could be less too, since there could be months in which some Sales Rep sold nothing at all. (so if no sales = no record in the sales table)

If I drag this half cooked measure against the year, here is how it looks

Seems in line.. lets proceed

 

Logic for slab wise calculation

It would be hard to explain in words, so consider this back-of-the-envelope calculation

1. We come back to RON whose monthly sales were 900.
2. In the 1st slab (for the first $100) he won’t get any commission.
3. In the 2nd slab he’ll get the commission on $400 (upper – lower i.e. 500-100). Makes sense right!
4. In the 3rd slab he’ll again get commission on $400 (900-500 i.e. total sales – lower).

Notice a few things

1. I find the Sales Value for commission = (Total Sales – Lower) – (Total Sales – Upper)
2. I adjust for the above for negative values if any
3. I then multiply Sales Value for Commission by Commission Rate%
4. And this repeats row by row, for each salesman, each month.

 

Slab wise calculation measure

Now let’s complete our measure. Adding the red part to our previous code.

Commission = 
VAR MonthlySalesTable =     
    ADDCOLUMNS(
        SUMMARIZE(
            SalesData,
            'Calendar'[Year],
            'Calendar'[Month],
            SalesData[Salesman]
        ),
        'Monthly Sales',
        [Total Sales]
    )
RETURN
    SUMX(
        MonthlySalesTable,
        VAR CurrentRowSales = [Monthly Sales]
        RETURN
        SUMX(
            CommTable,
            VAR CurrentUpperBound = CALCULATE(MAX(CommTable[Upper]))
            VAR CurrentLowerBound = CALCULATE(MAX(CommTable[Lower]))
            VAR SalesValueForCommission = 
                MAX(CurrentRowSales - CurrentLowerBound, 0)-
                MAX(CurrentRowSales - CurrentUpperBound, 0) 
            RETURN 
                SalesValueForCommission * CommTable[Commission]
        )
    )

I know it looks intimidating but if you understood the logic above, you’d understand the explainer story.

Using SUMX

1. I do calculation for each row of the Summarized Monthly Sales Table.
2. And since my Comm Table is not related to any other table so when I loop another SUMX for comm table, I get to work with all the commission slabs row by row without any filter context.
3. And for each commission slab, I follow the same logic as above.

Slightly tricky but worth it (you still with me?)

 

Now consider 3 different pivots, the results look so pretty 🙂

 

Couple of things..

1. If you see this calculation at granular level deeper than the month level (may be day level) this would give you a wrong answer.
2. In that case, you’ll probably have to use HASONEVALUE to turn off the day level calculation.

If you still haven’t understood any of what you’ve read, I totally understand your frustration, DAX can be tricky. If you need help, hit me up in the comments. I’ll be happy to help.

 

 

More on DAX Calculations

1. A similar post from Power Pivot Pro
2. TOP selling Product Analysis using DAX 
3. Least selling Product Analysis using DAX
4. Financial Year Calculations
5. Adjusted SamePeriodLastYear Calculations
6. Actual v/s Budget Calculation Patterns