Incorrect calculation with blank sum

[PBI_newuser]

Hi, I have a measure to calculate the Volume Impact. But it is incorrect when the [Volume Sum] is blank. 

If the [Volume Sum] is blank, it should be set as 0.

For example, Product A in 2020-10, the Volume Impact should be (0-1)*313 = -313 but it shows 395 which is incorrect.

The [Volume Impact Total] should has the same value as [Revenue YoY] for those lines highlighted in red.

How to fix the measure? Here is the pbix file.

[MFelix]

HI @PBI_newuser 

This is related with the fact that the values are blank has you refer you can do one of two things for the  Volume Last Year metrics to be 0  in the measure it self or within the other metric.

To force the metric it self change it to:

Volume last year = CALCULATE([Volume Sum],SAMEPERIODLASTYEAR('Calendar'[Date])) + 0

This however makes the measure to be always 0 and not blank so will present on other rows that you may not want it.

The second option is to redo your Total volume to the following:

Volume Impact = 
var TotalSalesCalculation =[Volume Last Year] + 0
Return
  IF (
    MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
    CALCULATE (
        SUMX (
            SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ,'Append Table'[Product]),
            IF (
                TotalSalesCalculation <= 0,
                ( [Revenue] - [Revenue Last Year] ),
                ( [Volume Sum] - TotalSalesCalculation ) * [AUSP Last Year]
            )),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )*-1,
    SUMX (
        SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth],'Append Table'[Product] ),
        IF (
            TotalSalesCalculation <= 0,
            ( [Revenue] - [Revenue Last Year] ),
            ( [Volume Sum] - TotalSalesCalculation  ) * [AUSP Last Year]
        ) ))

Has you can see we are calculation the Volume Last Year and adding the 0 so them you can compare it and get expected result.

[PBI_newuser]

Hi @MFelix , thank you for the modified measures. After applying the suggested measures, the value doesn't seem correct too. [Volume Impact] for product A supposed to have same value as [Revenue YoY].

[MFelix]

Did you do + 0 or =0

[PBI_newuser]

Hi @MFelix , yes, I have modified the measures based on your suggestion. I am not sure which part is incorrect, would you mind to take a look at it? Here is the pbix file.

[Ashish_Mathur]

Access Denied message.

[PBI_newuser]

Hi @Ashish_Mathur , can you try this?

https://drive.google.com/file/d/170wVIOYk5pr4cQj3NYHnWp6_ouzK_Adn/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Measure = ([Volume Sum]-[Volume last year])*[AUSP Last Year]

Measure 2 = if(HASONEFILTER('Calendar'[YearMonth]),[Measure],sumx(summarize('Calendar','Calendar'[YearMonth],"ABCD",[Measure]),[ABCD]))

The Grand total does not match but am unable to find out my mistake.

Hope this helps.