cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated Measure Total Incorrect

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

5 REPLIES 5
Super Contributor

## Re: Calculated Measure Total Incorrect

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])))*`

Super Contributor

## Re: Calculated Measure Total Incorrect

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]```
Super Contributor

## Re: Calculated Measure Total Incorrect

One more... and the pbix Net Profit

Frequent Visitor

## Re: Calculated Measure Total Incorrect

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

Frequent Visitor

## Re: Calculated Measure Total Incorrect

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.

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,348)