Calculated Measure Total Incorrect

[repsol]

Hi All,

I have a measure which calculates commission for products based on sales amount on weekly basis. The commission is based on a scale which varies based on sales amount. 

Sample Data:

Start Date	End Date	Product	Sales Amt
1/1/2019	1/7/2019	Product 1	1500
1/1/2019	1/7/2019	Product 2	2000
1/1/2019	1/7/2019	Product 3	2500
1/8/2019	1/14/2019	Product 1	2250
1/8/2019	1/14/2019	Product 2	3200
1/8/2019	1/14/2019	Product 3	1800
1/8/2019	1/14/2019	Product 4	800

Commission % =

SWITCH (

    TRUE  (),

    [SAMT]<=2000,(.30),

    [SAMT]>2000 && [SAMT]<=2500,(.40),

    [SAMT]>2500 && [SAMT]<=4000,(.50),

    [SAMT]>4000,(.60)

   )

SAMT is the Sales amount.

I have two other measures:

Commission = [Commission %] * [SAMT]

Net Profit = [SAMT] - [Commission]

When i select a given week in date range my calculations look accurate. If i select data range which includes both

weeks, my Commission and Net Profit are inaccurate. The commission is being calculated based on commission % which is taking cumulative sales amount for both weeks into consideration.

What formula should i use for Net Profit and Commission so that the Commission and Net Profit aggregates the individual weekly calculations when the two week date ranmge is selected? 

Thanks

[Nathaniel_C]

One more... and the pbix Net Profit

[repsol]

Hi,

Thanks for helping me. I dont have start and end dates as part of actual table. Date is just a slicer. Minute we remove date from table and push to slicer we see only one instance of the product for the entire time span. This is where the calculations are messing up. 

Thanks

[repsol]

 Should I create a new table for commsion and commission percentage and join to sales table for this to work? As of now in my model i just have the sales table and date table. 

[Nathaniel_C]

Hi @repsol ,

Note the totals are all correct.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Co % m = SWITCH (
    TRUE  (),   
    [Per Week m]>4000,.60,
    [Per Week m]>2500 ,.50,
    [Per Week m]>2000, .40,
    [Per Week m]<=2000,.30)
Note this may be simpler, as the value is decided when the test is true.

--------------------------
Per Week M = CALCULATE(Sum(Commish[Sales Amt]),FILTER(ALLEXCEPT(Commish,Commish[Start Date]),MAX(Commish[Start Date])))

--------------------------

Net Profit = [Per Week M]-Commish[Commission]

[Nathaniel_C]

Hi @repsol ,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Per Week = CALCULATE(Sum(Commish[Sales Amt]),FILTER(ALLEXCEPT(Commish,Commish[Start Date]),MAX(Commish[Start Date])))*