The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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.
User | Count |
---|---|
58 | |
56 | |
53 | |
49 | |
32 |
User | Count |
---|---|
173 | |
89 | |
70 | |
46 | |
45 |