Forum Discussion

mikeoshields's avatar
mikeoshields
Helper I
3 years ago
Solved

Difference between two rows in a matrix (row subdifferences)

I've searched through the community for an answer to this but haven't found one yet. Where there would normally be Row Subtotals, I need to have row subdifferences.  The first image below is the matrix in PowerBI, the second is an excel example of what I need.  Any help would be appreciated.

 

 

 

 

Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi mikeoshields ,

     

    Measure can only be placed in the value of the matrix view, and the view you expect cannot be achieved with a matrix.

     

    This can currently be achieved:

    Sample data:

     

    Measure:

    Difference =
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        [UCT_CD] = SELECTEDVALUE ( 'Table'[UCT_CD] )
            && [Date] = SELECTEDVALUE ( 'Table'[Date] )
    )
)
    - CALCULATE (
        MIN ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [UCT_CD] = SELECTEDVALUE ( 'Table'[UCT_CD] )
                && [Date] = SELECTEDVALUE ( 'Table'[Date] )
        )
    )

     

    The result is:

     

                  

     

    Hope this helps you.

     

    Best Regards,

    Community Support Team _Yinliw

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

1 Reply

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    3 years ago

    Hi mikeoshields ,

     

    Measure can only be placed in the value of the matrix view, and the view you expect cannot be achieved with a matrix.

     

    This can currently be achieved:

    Sample data:

     

    Measure:

    Difference =
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        [UCT_CD] = SELECTEDVALUE ( 'Table'[UCT_CD] )
            && [Date] = SELECTEDVALUE ( 'Table'[Date] )
    )
)
    - CALCULATE (
        MIN ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [UCT_CD] = SELECTEDVALUE ( 'Table'[UCT_CD] )
                && [Date] = SELECTEDVALUE ( 'Table'[Date] )
        )
    )

     

    The result is:

     

                  

     

    Hope this helps you.

     

    Best Regards,

    Community Support Team _Yinliw

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