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.
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.
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |