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

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

Gheb_Gabriela ,

 

You mean that the matrix lines can be blank correct liken the image below the category dissapears:

 

In this case best option is to select the visual and on the line values select the option Show items with no Data:

 

Gheb_Gabriela's avatar
Gheb_Gabriela
Frequent Visitor
4 years ago

MFelix i've tried this solution from the begging, but no... it's not showing "0". Maybe, it's from my database... the column with the values should be formatted different or something like this.

 

Thanks

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

    Try to rearrange the measure to:

    Adjusted Value = 


IF (
    HASONEVALUE ( 'Table'[Cat] ),
    SUM ( 'Table'[Value] ) + 0,
    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] )
            )
        )
)

     

     

     

    The +0 is only on the first part of the IF statement,