Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm having some problems getting a calculation working and need some assistance.
I'm trying to calculate discount for various products YTD. Some products have a base price and sale price, some just have sale price.
In the event that there is no base price, these products should be excluded from discount sums. Take the following example:
Item | Sales Value | Base Price | Discount |
Group1 | € 4,185.00 | € 2,228.56 | 87.79% |
Product 1 | € 2,360.00 | € 2,228.56 | 5.90% |
Product 2 | € 1,825.00 |
As you can see Group 1 discount is calculating: (sum of sales value - sum of base price) / (sum of base price). Group 1 should not be taking Product 2 into account for cumulative calculations for discount.
Here is my Dax formula as is:
Total Discount YTD (Actual):=
IF(
AND(
CALCULATE(SUM(SalesActualVsForecast[Amount]),DATESYTD(DateDimension[Date]),SalesActualVsForecast[Type]="Actual") <> BLANK(),
CALCULATE(SUM(SalesActualVsForecast[BasePrice]),DATESYTD(DateDimension[Date]),SalesActualVsForecast[Type]="Actual") <> BLANK()),
DIVIDE(
(
CALCULATE(SUM(SalesActualVsForecast[Amount]),DATESYTD(DateDimension[Date]),SalesActualVsForecast[Type]="Actual")
-CALCULATE(SUM(SalesActualVsForecast[BasePrice]),DATESYTD(DateDimension[Date]),SalesActualVsForecast[Type]="Actual")),
CALCULATE(SUM(SalesActualVsForecast[BasePrice]),DATESYTD(DateDimension[Date]),SalesActualVsForecast[Type]="Actual")
)
)
Solved! Go to Solution.
You may try using SUMX Function which returns the sum of an expression evaluated for each row in a table.
You may try using SUMX Function which returns the sum of an expression evaluated for each row in a table.