What is the correct context when making a formula with a what if parameter?

DAX DAX Calculations
1

I am trying to multiply a what if parameters but I am struggling to define the correct context.

In my case, we an incentive program which provides 3 different rebates. Rebate 2 & 3 are already determined but rebate 1 is the difference between Total incentives - rebate 2 and 3. While total incentives is a dynamic function depending on the net profit.

In my model, Rebate 1 calculation does not apply correctly to all the customer. My context is probably wrong but I am unable to use the correct filters to get the “rebate 1 %” applied to all customers.

Any thoughts?

See attached the PBIX data file
Rebate example.pbix (38.0 KB)

2

Hi @JayLocher,

I don’t understand the problem you are encountering.
I verified your formula and the results and I’ve not found something wrong.

When I see all the results in the table, it seems ok.
image

So I think I didn’t understand your problem well…
Can you please try to explain it again ?
And maybe you could give the results you are waiting for.

Best regards,
Joaly

3

Hi @JBocher ,

Sorry if I wasn’t clear. Considering a net profit of 3380, “% of rebate 1”, should be set at 35%, this is calculated with the “Rebate 1 %” measure. This % applies to all customer for rebate 1.

image
image983Ă—146 5.72 KB

In the table you sent, the rebate 1 % is set at 97% for customer 1 because the calculation is considering 1311.50 as rebate 1 total whereas I only need the 35% to be multiplied by the annual revenue.

I would like to filter the 35% result and apply it to every single line item.

Does it clarify the issue?
Thanks

4

@JayLocher,

Thank you for your clarification.

I’ve changed the formula for Rebate 1 % in order to have 35% for each customer.
I’ve only changed the context of the calculation by adding a “ALL” function for the customers name.

image
image1004Ă—40 2.92 KB

The table is now this one :
image

Is this the results you want ?

I hope it will help you.

The solution file :
Rebate example.pbix (39.3 KB)

Best regards,
Joaly

5

@jbocher

Thank you so much for looking at this. This is definitely working on my example file. I felt a bit embarrassed on how easy this was.

Even if it works on the example file, it does not work in my model.

I need to dig into this to understand why it does not work on my side.

Thanks again
Best regards

6

@JayLocher

I hope you will find the solution for your model.
If it’s not the case and you have any questions, feel free to ask :wink:

Best regards,
Joaly

7

So I was able to identify what is not working but I am not sure why.

My model has an additional layer which I have added to the example file.

I have added a new measure “Rebate 1 at 100%”. This represents how much rebate would represent if this was paid at 100%.

To define “Rebate 1 %”, I divide the current rebate 1 Total by “Rebate 1 at 100%” . Based on the printscreen below the result is 27%

image
image801Ă—70 3.04 KB

However, this is not the what I get in the table, the column shows weird results.

image
image898Ă—439 128 KB

Interestingly enough, if I remove the “allowance” row, then the % is correct, but the “rebate 1 value” is not the expected value.

image
image863Ă—424 123 KB

Any toughts?
Best regards
Rebate example.pbix (38.5 KB)

8

Hi @JayLocher,

I managed to understand why there is a problem.
You need to change the Rebate 1% like that :
image

I’ve changed the ALL( Data[ Customer Name] ) with ALL( Data ).
In that way, you can add on your table all the Data columns you want and the result will not change.

Nonetheless, the best practice is to always create measures to show data numbers on tables and not use the data directly.

So I’ve created a Total Annual Revenue measure
image

And another one about allowance
image

Then I’ve changed the measure Rebate 1 value just to use the Total Annual Revenue I’ve created
image

With all the changes have made, the formulas seem to work.
Here is the result :

image
image994Ă—444 27.1 KB

I hope this time it will answer to your problem.
Rebate example (1).pbix (39.4 KB)

Best regards,
Joaly

1 Like
9

@JBocher ,

Thank you so much. It is working very well and thank you for the tips regarding the best practice, i will try to apply this on other measures that I do have on my side.

It is somewhat amazing that the solution was so easy - meaning applying ALL (DATA) -instead of the first solution - but I struggled so much with this.

Thanks again for your assistance - this is best valuable assistance.
Best regards

1 Like