cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IoanCosmin Regular Visitor
Regular Visitor

Switch DAX rebate based on cumulative measure

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)
        )

 rebateexample.png

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Switch DAX rebate based on cumulative measure

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] ) )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Switch DAX rebate based on cumulative measure

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 %] 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


IoanCosmin Regular Visitor
Regular Visitor

Re: Switch DAX rebate based on cumulative measure

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?

 

 

wrongtotal.png

 

 

Community Support Team
Community Support Team

Re: Switch DAX rebate based on cumulative measure

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] ) )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
IoanCosmin Regular Visitor
Regular Visitor

Re: Switch DAX rebate based on cumulative measure

Hello @v-cherch-msft

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

I appreciate your help.

 

Best,

C