Forum Discussion

dimitrishuk's avatar
dimitrishuk
Helper I
8 years ago
Solved

Exclude blank rows from sums

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:

 

ItemSales ValueBase PriceDiscount
Group1€ 4,185.00€ 2,228.5687.79%
          Product 1€ 2,360.00€ 2,228.565.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")
        )
)