cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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)
)```

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

## Re: Switch DAX rebate based on cumulative measure

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

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

## 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!

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?

Community Support Team

## Re: Switch DAX rebate based on cumulative measure

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

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