Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
One more... and the pbix Net Profit
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
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.
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]
Proud to be a 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])))*
Proud to be a Super User!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |