Forum Discussion

Gheb_Gabriela's avatar
Gheb_Gabriela
Frequent Visitor
4 years ago
Solved

MEASURE for totals from matrix

Hi all,

 

I have a question : is there any options to use totals from a matrix to calculate a difference? I want to use the totals from matrix, to make a difference between years.

So, in the example below i have the amounts and I want to use them to make a difference between all the totals from matrixs.

 

Thank you

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Gheb_Gabriela ,

     

    For this you need to redo the formula you use for the matrix values. I have used the following data for example:

     

    Now add the following measure to your dataset:

    Adjusted Value =
IF (
    HASONEVALUE ( 'Table'[Cat] ),
    SUM ( 'Table'[Value] ),
    SUM ( 'Table'[Value] )
        - CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Year], 'Table'[Quarter], 'Table'[Time] ),
                'Table'[Year]
                    = SELECTEDVALUE ( 'Table'[Year] ) - 1
                    && 'Table'[Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
            )
        )
)

     

     

    Check PBIX file attach.

7 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi Gheb_Gabriela ,

     

    For this you need to redo the formula you use for the matrix values. I have used the following data for example:

     

    Now add the following measure to your dataset:

    Adjusted Value =
IF (
    HASONEVALUE ( 'Table'[Cat] ),
    SUM ( 'Table'[Value] ),
    SUM ( 'Table'[Value] )
        - CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Year], 'Table'[Quarter], 'Table'[Time] ),
                'Table'[Year]
                    = SELECTEDVALUE ( 'Table'[Year] ) - 1
                    && 'Table'[Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
            )
        )
)

     

     

    Check PBIX file attach.

    • Gheb_Gabriela's avatar
      Gheb_Gabriela
      Frequent Visitor
      4 years ago

      MFelix Thanks for your solution, it's working for what I need, except that i have blanks instead 0 and everything i've tried it's not working. I have used on your formula +0. Maybe in the pbix that you have attached, you can show me a sollution? To can replace blanks in the matrix with 0?

       

      Have a great day!

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        Hi Gheb_Gabriela ,

         

        What do you mean by you have blanks instead of 0? In my model the values are also blanks and not 0. Can you please provide further insight on that part?