Switch DAX rebate based on cumulative measure

[IoanCosmin]

I am having a bit of tough time wrapping my head around this. I have a cumulative total based on hours and I need to calculate the rebate. The rule here is that after that total exceeds an amount, change the rebate percentage (10%, 15%). Regardless how I try it, I end up with the wrong total or the wrong percentage.

Here's my failed approach:

+------------+-------------+------------+
| Day        | BilledHours | LaborPrice |
+------------+-------------+------------+
| 07/01/2018 | 98          | 13000      |
+------------+-------------+------------+
| 07/02/2018 | 89          | 12000      |
+------------+-------------+------------+
| 07/03/2018 | 80          | 11000      |
+------------+-------------+------------+
| 07/04/2018 | 92          | 9000       |
+------------+-------------+------------+
| 07/05/2018 | 52          | 8000       |
+------------+-------------+------------+
| 07/06/2018 | 73          | 7000       |
+------------+-------------+------------+
| 07/07/2018 | 82          | 11000      |
+------------+-------------+------------+


Cumulative Hours = 
CALCULATE(
    SUM(Rebates[BilledHours]),
    FILTER(
        ALL(Rebates[Day]),
        Rebates[Day]<=MAX(Rebates[Day]))
        )

Rebate = 
SUMX(Rebates,
    SWITCH(
        TRUE(),
        [Cumulative Hours]<=400,Rebates[LaborPrice] * 0.10,
        [Cumulative Hours]>=401,Rebates[LaborPrice] * 0.15)
        )

 

How can I achieve the correct rebate and total? If I use the SWITCH before the sum, I end up with the incorrect total (since the total is always > 400).  I chose SWITCH because there are other conditions that I have to add for different categories (e.g. if the cumulated hours in the past quarter are < x). How can I store this in a variable and solve the issue?

Thank you

[Greg_Deckler]

By putting that SWITCH within a SUMX, you are probably doing something that is causing unexpected filtering issues. Try this:

Rebate % = 
    SWITCH(
        TRUE(),
        [Cumulative Hours]<=400,.1,
        [Cumulative Hours]>=401,.15)

Rebate = SUM([LaborPrice]) * [Rebate %] 

[IoanCosmin]

Greg, I really appreciate your suggestion. I tried your approach on the real dataset hoping that it will be precisely the solution I am seeking. However, I get the right percentage but the wrong total since the total is always > 400. It works as expected for the sample that I have provided but on the real dataset I have multiple IDs with same date that roll up to that amount. Not sure how can I get around the context transition. Can you please show me how can I solve it?

[v-cherch-msft · Accepted solution]

Hi @IoanCosmin

You may try to use IF Function to get the correct total value. For example:

Total  =
VAR a =
    SUMMARIZE ( Rebates, Rebates[Day], "a1", Rebates[Rebate] )
RETURN
    IF ( ISFILTERED ( Rebates[Day] ), Rebates[Rebate], SUMX ( a, [a1] ) )

Regards,

Cherie

[IoanCosmin]

Hello @v-cherch-msft

Excellent solution, this is exactly what I was looking for! Thank you, Cherie.

I appreciate your help.

Best,

C