Incorrect Totals for measure when blanks present in Power BI

1
votes

I have a selector slicer TYPE (based on sum of quantity or sum of sales).

If Sales or None is selected, TYPE=sum(FactSales[Sales]) is calculated and if Quantity then TYPE=sum(FactSales[Quantity]).

There is another measure for Same Period Last Year using

TYPE LY = SAMEPERIODLASTYEAR(DimDate[DateFormat].[Date])`

Next measure is difference between this year and last year as

variance = IF(AND(NOT(ISBLANK([Type])),NOT(ISBLANK([Type LY]))),CALCULATE([Type]-[Type LY]),BLANK())

There are some customer whose this year or last year sales may be blank or 0. the table shows correct value but the total is wrong because it add up even for blanks:

screenshot

I have been stuck on this issue for a while now, this wrong total shows wrong value in multi-row card for each customer. Any suggestions are welcome. Thanks!

1
powerbidaxmeasure

1 Answers

2
votes

For that output, you need to iterate over the Year values, and only calculate the sum for years where neither Type nor Type LY are blank:

Annual Variance = 
    SUMX ( 
        VALUES ( DimDate[Year] ),
        IF ( 
            NOT ( ISBLANK( [Type] ) ) && NOT ( ISBLANK ( [Type LY] ) ),
            [Type] - [Type LY],
            BLANK()
        )
    )