Calculation within Scaling model

[Vossberger]

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

[Bmejia]

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.

[Vossberger]

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

[Vossberger]

This is how I would do it in Excel (VLookup formula)

[Arul]

@Vossberger ,

Still I could not understand, sorry. If you could add more details in a detailed manner may be that would help.

Thanks,

Arul

[Vossberger]

Well I don't know how to describe it to you .... maybe the picture helps. I'm missing a formula (yellow box).

[Vossberger]

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.

[Arul]

@Vossberger ,

Do you want to calculate percentage of sales out of total?

Thanks,

Arul