Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hoping someone out there can help me. I want to calculate a customer rebate based on the cumulative sales multiplied by a % when sales reach certain levels, eg:
FYTD Sales > 400K , FYTD Sales * 0 ,
FYTD Sales > 400K && FYTD Sales < 500K , FYTD Sales * 0.02
(Above only on the sales between 400K && 500K = 100K * 0.02 = 2K)
FYTD Sales >500K, FYTD Sales * 0.045 (Only on the sales above 500k)
I'm trying create the measure: tried if and switch statements, on cumulatve sales and Sales DatesYTD, but can't get it to work.
Problems I'm having are either I calculate the right figures but it won't display properly over months (to show montly calculation ), or the measure shows correctly over months but calculates incorrect figures ( on the whole sales including the first 400).
Anyone any ideas or done something similar, appreciate all the help I can get,.
Thanks
Solved! Go to Solution.
try this, I've put the input values as variables so it's easier to adjust in the future
Rebate = VAR AmountSold = [FYTD Sales] VAR UpperValue = 500000 VAR UpperRebate = 0.045 VAR LowerValue = 400000 VAR LowerRebate = 0.02 RETURN IF ( AmountSold > UpperValue, ( AmountSold - UpperValue ) * UpperRebate + ( UpperValue - LowerValue ) * LowerRebate, IF ( AmountSold > LowerValue, ( AmountSold - LowerValue ) * LowerRebate, 0 ) )
Hi @CMAC_Terry,
Please follow the solution @Stachu posted. Please mark the reply as answer if you have resolved your issue. Please share your sample table to test if you still have problems.
Thanks,
Angelia
try this, I've put the input values as variables so it's easier to adjust in the future
Rebate = VAR AmountSold = [FYTD Sales] VAR UpperValue = 500000 VAR UpperRebate = 0.045 VAR LowerValue = 400000 VAR LowerRebate = 0.02 RETURN IF ( AmountSold > UpperValue, ( AmountSold - UpperValue ) * UpperRebate + ( UpperValue - LowerValue ) * LowerRebate, IF ( AmountSold > LowerValue, ( AmountSold - LowerValue ) * LowerRebate, 0 ) )
Thanks for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |