Exclude blank rows from sums

[dimitrishuk]

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")
        )
)

[v-chuncz-msft · Accepted solution]

@dimitrishuk,

You may try using SUMX Function which returns the sum of an expression evaluated for each row in a table.