Sales Rebate Calculation

Hi
Sales Dashboard Test.pbix (144.6 KB)
Example.xlsx (14.8 KB)

Have been working on a rebate calculation within our sales file. Rebate percentage changes based upon month. Whilst I have the rebate percentage by month I cannot get the total for the year. I attach my test file and an example of what it should look like.

Thanks in advance.

See if it helps

06 Rebate Value = 
SUMX( 
    VALUES( Dates[MonthInCalendar] ),
    [05 Rebate Percentage]*[04 Warehouse Rebate]
)

image

Hey Michele,

I am not sure why you use that logic in your measure, it seems that you summarize the distinct Rebate Percentages and then iterate over them to sum their values.

05 Rebate Percentage = 
SUMX(
    SUMMARIZE(
        'Rebate Percentage',
        'Rebate Percentage'[Rebate Percentage]
    ), 
    'Rebate Percentage'[Rebate Percentage]
)

So, in the case there multiple same RebatePercentages, they are only iterated once, since you summarize by that column.

A better option would be to use this measure that produces the expected sums as in your excel:

05b Rebate Percentage = SUM( 'Rebate Percentage'[Rebate Percentage] )