Forum Discussion
Calculation within Scaling model
Hi,
I'm trying to implement a calculating "Scaling Modell" and do not find a solution how to deal with it.
Example :
SUM of Sales ist 1'234 and I'm willing to use that value to determine a percentige within a table (could contain much more
thresholds), in this case 3.5% would be the result, or with Sales of 1'001 = 2.5%.
| SUM Sales | Rebate |
| 900 | 2.0% |
| 1'000 | 2.5% |
| 1'100 | 3.0% |
| 1'200 | 3.5% |
| 1'300 | 4.5% |
| 1'400 | 5.0% |
| ... | ... |
Does anybody have a solution for me?
KR
7 Replies
Hi Arul,
I have a "Rebate Scaling Table" (as shown above) - I am looking for a formula that will then match a SUM out of a Sales formula to this "Rebate Scaling Table" and use the correct/corresponding percentage to calculate.
This is how I would do it in Excel (VLookup formula)
Still I could not understand, sorry. If you could add more details in a detailed manner may be that would help.
Thanks,
Arul
Well I don't know how to describe it to you .... maybe the picture helps. I'm missing a formula (yellow box).
Hopefully something like this helps.
1. Create a Total Sales Measure something like this:
Total Sales Amount =CALCULATE(SUM(Sales[Total Sales Amount]))2. Create a new column something like this:Resulting Rebate =If([Total Sales Amount]>=Scaling_Table[Sales] ,Scaling_Table[Rebate],BLANK())3. Insert a card and enter the Resulting Rebate and Show only the Last Value.. You can also create a measure that gives you the max value.Thank you very much Bmejia, I somehow found out how to deal with your input 🙂 and it is working.
Anyhow my issue now is that I cant apply 4 different filters (Calender ranges(variable), and different types of flags) on this calculation and I have to find out if I can handle it with the function keepfilteers.
Kind Regards
