cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
repsol Frequent Visitor
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 DateEnd DateProductSales Amt
1/1/20191/7/2019Product 11500
1/1/20191/7/2019Product 22000
1/1/20191/7/2019Product 32500
1/8/20191/14/2019Product 12250
1/8/20191/14/2019Product 23200
1/8/20191/14/2019Product 31800
1/8/20191/14/2019Product 4800

 

 

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
Nathaniel_C Super Contributor
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])))*
 
Nathaniel_C Super Contributor
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

Commish.PNG

 

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

Re: Calculated Measure Total Incorrect

One more... and the pbix Net ProfitCommish1.PNG

 

repsol Frequent Visitor
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

repsol Frequent Visitor
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. 

Helpful resources

Announcements
New Kudos Given Badges Coming

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

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

Power Platform World Tour

Find out where you can attend!

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