Calculate discounted rate from discount table

[msommerf]

Hi, please could somebody help with the following?

I am attempting to work out a discount to be applied to my total sales as follows:

I have a rebate table:

Manufacturer     £0     £10     £20     £30     £40     £50

Man 1                 0%     0%     1%      1%      2%     2%

Man 2                 0%     0%      0%      0%      1%     1%

So for Man 1.........

If sales between £0 and £9.99 - 0% discount applied

If sales between £20 and £29.99 - 1% discount applied

Etc..

I have another table which shows my total sales:

Manufacturer     Units     Total Sales   Rebate

Man 1                    2           £25             ?

Man 2                    1           £60             ?

I would like to be able to take my total sales figure and look up the discount to be applied using the rebate table.

So for Man 1 with sales of £25 I would see a discount of 1% and for Man 2 a discount of 1%.

I am just getting started with DAX so any help appreciated.

[v-chuncz-msft · Accepted solution]

@msommerf ,

You may select Unpivot Columns in Query Editor first, then refer to the post below.

https://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228