Forum Discussion

julpol's avatar
julpol
Regular Visitor
1 year ago
Solved

Multiple variance calculation in matrix

Hi everyone

 

I'm doing a report in which I have multiple years as columns and some categories as rows. I would like to see two variance columns at the end, first as a difference between the last year's value and previous year's value, and the second variance would should the %.

 

 

I tried creating the first measure but i'm not able to get the value for those two years. Could you please review the DAX code below and advise what I'm missing there? Obviously the filter does not work as intended, but why?

 

If I replace the BLANK() with MaxYear, the system shows correctly 2018

 

If I replace the BLANK() with PrevYea, the system shows correctly 2017

If I replace the BLANK() with MaxYearValue, the system shows incrrectly SUM of values from all years, not only the last two.

 

Variance $ = 
VAR MaxYear = CALCULATE(MAX('Table'[Year]))
VAR PrevYear = MaxYear-1
VAR MaxYearValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MaxYear))
VAR PreviousYearValue = COALESCE(CALCULATE(SUM('Table'[Value]),'Table'[Year] = PrevYear),0)

RETURN 
IF(
    ISINSCOPE('Table'[Year]) ,
    MaxYearValue - PreviousYearValue,
   BLANK()
)

 

If I add another the second measure into the matrix, both measures are displayed for each year. Why and how do I fix?

 

 

Thank you

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi julpol ,

     

    Did I solve your problem? Or you can also try turning off the following two options:

     

    Best regards,
    Community Support Team_ Scott Chang

     

     

8 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    julpol ,

    First, let's create measures to calculate the values for the maximum year and the previous year.

    DAX
    MaxYearValue =
    VAR MaxYear = CALCULATE(MAX('Table'[Year]))
    RETURN CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)

     

    DAX
    PreviousYearValue =
    VAR MaxYear = CALCULATE(MAX('Table'[Year]))
    VAR PrevYear = MaxYear - 1
    RETURN CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)

     

    Then create a mesure for Variance

     Variance$ =
       VAR MaxYear = CALCULATE(MAX('Table'[Year]))
       VAR PrevYear = MaxYear - 1
       VAR MaxYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)
       VAR PreviousYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)
       RETURN
       IF(
           ISINSCOPE('Table'[Year]),
           MaxYearValue - PreviousYearValue,
           BLANK()
       )
     
    Then one measure for Variance %
    DAX
    Variance% =
    VAR MaxYear = CALCULATE(MAX('Table'[Year]))
    VAR PrevYear = MaxYear - 1
    VAR MaxYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)
    VAR PreviousYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)
    RETURN
    IF(
    ISINSCOPE('Table'[Year]),
    DIVIDE(MaxYearValue - PreviousYearValue, PreviousYearValue, 0),
    BLANK()
    )
    • julpol's avatar
      julpol
      Regular Visitor
      1 year ago

      Hi bhanu_gautam 

       

      unfortunately, the result is the same, 


      1) both variance measures are blank in the matrix

      2) when both are used, they are added to every column

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi julpol ,

         

        There is no problem with your calculation logic. The problem is because you are putting multiple fields in the Values field of the matrix.For an matrix. multiple fields can be put in the row, which represents the hierarchy, whereas both columns and Values should have only one aggregated field.

         

        Hope it helps!

         

        Best regards,
        Community Support Team_ Scott Chang

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.