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

[djillbunters]

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 want to, if possible, apply conditional formatting on values ONLY in the Delta column.

It seems not possible?

Example formula for the Total Revenue:

Total Revenue = 
VAR __Category = SELECTEDVALUE('DisconnectedTable'[Classification], BLANK())
VAR SelectedType = SELECTEDVALUE(Sheet1[Type])

VAR __Result = 
SWITCH( TRUE(), 
__Category = "Actual", 
-SUMX(
FILTER('BudgetActual', 
[Classification] = "Actual" && 
RELATED(GLAccountClassification[Index]) IN {1,2}
),
[Amount]
),
__Category = "Budget", 
-SUMX(
FILTER('BudgetActual', 
[Classification] = SelectedType && 
RELATED(GLAccountClassification[Index]) IN {1,2}
),
[Amount]
),
__Category = "Delta", 
-SUMX(
FILTER('BudgetActual', 
[Classification] = "Actual" && 
RELATED(GLAccountClassification[Index]) IN {1,2}
),
[Amount]) -
-SUMX(
FILTER('BudgetActual', 
[Classification] = SelectedType && 
RELATED(GLAccountClassification[Index]) IN {1,2}
),
[Amount]
),
__Category = "LY", 
-SUMX(
FILTER(ALL('BudgetActual'), 
[Classification] = "Actual" && 
RELATED(GLAccountClassification[Index]) IN {1,2} && 
YEAR(BudgetActual[Date]) = YEAR(MAX(BudgetActual[Date])) - 1 &&
MONTH(BudgetActual[Date]) IN VALUES('Date'[Date].[MonthNo])
),
[Amount]
),
// Default case for any other value of __Category
BLANK()
)

RETURN
__Result

Maybe I'm going all wrong about it?

Maybe I should start over and use a Measure for Actual, Delta, Budget, LY instead, and use a different table for a hierarchy?

I did it this way because the person I'm making it for likes to see things in a very specific way.

My P&L Forecast I built differently with just 1 measure and some custom DAX for GROSSMARGIN etc... (as it's a calculation and not a GL account):

Preferably I would like to not start over, but if that's what needs to be done then so be it 🙂

Thanks in advance!

EDIT:
Could i create a custom measure that either calculates whether it should apply (only if = "Delta") and use this measure in the conditional formatting formula?

[v-xinruzhu-msft]

Hi @djillbunters 

It can be set directly.

e.g for the total revenue, you can create a measure. 

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

Then put it to the conditional formatting of the [Total Revenue] 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.

[djillbunters]

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")

[v-xinruzhu-msft · Accepted solution]

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]

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