Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
repsol
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 User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

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]




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.