Forum Discussion

djillbunters's avatar
djillbunters
Frequent Visitor
2 years ago
Solved

Matrix table with measures as rows - conditional formatting on specific Column only

Dear all,   I have an annoying but useful Matrix table, which I use to calculate Actual, Budget, Delta & LY. Each row is a measure and using a disconnected table I determined my columns.   Now I...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi djillbunters 

    You need to set the condition at different measures, it cannot be put in one measure, you need to set it with multiple measure.

    If you want to implement it in one measure, you need to put all your one measures in one measure, you can refer to the following solution.

    1.Create a table,put all the measure names to the table. then put the column of the table to the row of the matrix. the column  of the table visual don't change.

    2.Create a measure,and put the measure to the value of the matrix.

     

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table'[Type] ) = "Total Revenue", [Total Revenue],
    SELECTEDVALUE ( 'Table'[Type] ) = "COGS", [COGS],
    SELECTEDVALUE ( 'Table'[Type] ) = "Gross Margin", [Gross Margin]
)

     

    3.Then create a measure to set the format.

     

    Measure2 =
IF (
    SELECTEDVALUE ( 'DisconnectedTable'[Classification] ) = "Delta"
        && [Measure] < 0,
    "red"
)

     

    Put the measure2 to the conditional formatting of the measure.

     

    Best Regards!

    Yolo Zhu

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

     

     

     

     

    Best Regards!

    Yolo Zhu

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

djillbunters's avatar
djillbunters
Frequent Visitor
2 years ago

Thank you, this seems to work!
However I'm struggling to put the entire logic in 1 measure.
So I can use 1 measure as conditional formatting on each measure in my table.
But this does not seem to work 

CondFormat_Delta =

VAR SelectedMeasure = SELECTEDMEASURE()

RETURN

IF (

    SELECTEDVALUE ( 'DisconnectedTable'[Classification] ) = "Delta"

        && SelectedMeasure < 0 ,

    "red")



Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi djillbunters 

You need to set the condition at different measures, it cannot be put in one measure, you need to set it with multiple measure.

If you want to implement it in one measure, you need to put all your one measures in one measure, you can refer to the following solution.

1.Create a table,put all the measure names to the table. then put the column of the table to the row of the matrix. the column  of the table visual don't change.

2.Create a measure,and put the measure to the value of the matrix.

 

Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table'[Type] ) = "Total Revenue", [Total Revenue],
    SELECTEDVALUE ( 'Table'[Type] ) = "COGS", [COGS],
    SELECTEDVALUE ( 'Table'[Type] ) = "Gross Margin", [Gross Margin]
)

 

3.Then create a measure to set the format.

 

Measure2 =
IF (
    SELECTEDVALUE ( 'DisconnectedTable'[Classification] ) = "Delta"
        && [Measure] < 0,
    "red"
)

 

Put the measure2 to the conditional formatting of the measure.

 

Best Regards!

Yolo Zhu

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

 

 

 

 

Best Regards!

Yolo Zhu

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

  • djillbunters's avatar
    djillbunters
    Frequent Visitor
    2 years ago

    Quite elegant solution! And works great.
    Thank you very much