Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incorrect calculation with blank sum

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.

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    HI Anonymous 

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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].