Forum Discussion

MrSnaggls's avatar
MrSnaggls
Frequent Visitor
1 year ago
Solved

Getting max value for each row in matrix

Hi,   I have a problem which haunts me for some days now. It didn't look that hard at first, but it turned out challenging. Overall target: I have a matrix with 3 dimensions as rows and 1 dimen...
  • DataInsights's avatar
    1 year ago

    MrSnaggls,

     

    Try these measures. The concept is to let the row fields naturally slice the data, but remove column filter context from within the visual (i.e. keep slicer/filter selections). You can use hex codes instead of color names.

     

    Sum Value = SUM ( Test_Table[Value] )
    Conditional Formatting = 
    VAR vValue = [Sum Value]
    // create table of all Column values per slicers/filters
    VAR vColumnValues =
        CALCULATETABLE (
            VALUES ( Test_Table[Column] ),
            ALLSELECTED ( Test_Table[Column] )
        )
    VAR vTable =
        ADDCOLUMNS ( vColumnValues, "@Value", [Sum Value] )
    VAR vRowMinValue =
        MINX ( vTable, [@Value] )
    VAR vRowMaxValue =
        MAXX ( vTable, [@Value] )
    VAR vResult =
        SWITCH ( TRUE, vValue = vRowMinValue, "Green", vValue = vRowMaxValue, "Red" )
    RETURN
        vResult

     

    Apply conditional formatting using the measure [Conditional Formatting]: