dynamic conditinal formatting based on mtrix condition

[Anonymous]

Hi there,

When you have a martix with Actuals and Budget columns the precentages and color coding make sense when the matrix colapsed but when you expand the matrix it dosen't make sense to have color coding because you can't compare line item to the overall budget (Let say office supply budget for the month is 1000 but when I expand I have 10 items 100 each so I can't compare line item to the budget)

My question how to make the color coding appear when the matrix is collapse and invisible when it expand?

Thanks,

Oded Dror

[Anonymous · Accepted solution]

Hi there,

This one works and I did conditional formatting based on that

Formatting =

VAR Percentage = [Actual Vs Budget]

VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||

    Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))

RETURN

If(CheckExpand = TRUE(),

   Percentage,0)

Thanks

Oded Dror

[MFelix]

Hi @Anonymous,

You should try something like

Conditional = IF(HASONEFILTER( TABLE[Column]) ; 0 ;1)

Then make the conditional formatting based on the 1.

You can also replace the 1 by the hex coding and then you just need to set it in the conditional formatting.

Regards,
MFelix

[Anonymous]

Hi MFlix,

I tried that but it still show conditional formatting when the matrix is expanded.

One thing my conditional colun is based on other column (Precent value) and adding another column make it impossible to achive.

If it was ability to do like SSRS when you can change the background colore based on condition with IF satement then your solution was apply.

Thanks,

Oded Dror

[MFelix]

Hi @Anonymous ,

I made a small example with the following setup:

Cat	SubCat	Value
A	A	100
A	B	200
B	A	100
B	B	100
C	A	20
C	B	50

Created the following measure:

Formatting = IF(NOT(HASONEVALUE('Table'[SubCat]) );"red")

Then made the condittional formating on values:

Two remarks:

• Use the NOT so that the expanded would not need to have the hex coding
• You can change the "Red" by the HEX code something like "#FF0000"

Be aware that when you expand one of the categories on the matrix table the condittional formatting will not aplly to any subtotals (this is behaviour on PBI not error).

Regards,

MFelix

[Anonymous]

Hi there,

You are checking the condition of the matrix if is expaned or collapsed that's one thing

The second thing is if it collapsed (then check) and subToal is over Budget below 10% then yellow over 10% red

And when the matrix is expanded don't show the background color, most likely I need to add calculate value too
to the IF statement, can you incorporate multiple IF statements and show me how to achive that? I need help on that.

Thanks,

Oded dror

[MFelix]

Hi @Anonymous ,

You can use a SWITCH function that evaluates an expression against a list of values and returns one of multiple possible result expressions.

Formatting =
VAR Percentage =
    ( SUM ( 'Table'[Value] ) / SUM ( 'Table'[Target] ) ) - 1
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'Table'[SubCat] ) )
RETURN
    SWITCH (
        TRUE ();
        CheckExpand
            && Percentage < 0,1; "Yellow";
        CheckExpand
            && Percentage > 0,1; "Red"
    )

You can change the Percentage formula by the one you think is more suitable to your calculations.

Regards,

MFelix

[Anonymous]

Hi there,

this is my calculation - no error there but when I put this measure in my marrix I'm getting this error (see below)

Formatting =
VAR Percentage =
   Divide(((Sum('ActualVsBudget'[Activity_YTD]) - Sum(ActualVsBudget[BUD_YTD]))) , Sum(ActualVsBudget[BUD_YTD]) )
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'GLedger'[Account]) )
RETURN
    SWITCH (
        TRUE(),
        CheckExpand
            && Percentage < 0,0.1, "Yellow",
        CheckExpand
            && Percentage > 0,0.1, "Red"
    )

Error Message:
MdxScript(Model) (34, 31) Calculation error in measure 'GLedger'[Formatting]:
Function 'SWITCH' does not support comparing values of type True/False with values of type Number.
Consider using the VALUE or FORMAT function to convert one of the values.

Thanks,

Oded Dror

[Anonymous]

Hi there,

I modify the measure and got it to work but it still showing flags when th ematrix is expandad 

Formatting =

VAR Percentage = [Actual Vs Budget]

VAR CheckExpand =

    NOT ( HASONEVALUE ( 'GLedger'[DD]) )

RETURN

    SWITCH (

        TRUE(),

        CheckExpand

            && Percentage >= 0.0 && Percentage <= 0.10, UNICHAR(128077),

        CheckExpand

            && Percentage > 0.10 && Percentage < 1.0,  UNICHAR(128681)

    )

Thanks,

Oded Dror

[MFelix]

Hi @Anonymous ,

Is the 'GLedger'[DD] the lowest level of your matrix? is it the last level when you expand your matrix?

Can you share a sample file?

Regards,

MFelix

[Anonymous]

Yes, but it dosen't seems working correctly.

I do have multiple column in the header that drill down and the measure hiding some of the values based on that.

I realize that we can't do conditional formatting on sub total or total without including the detail rows.

I gave up (Iv'e tried all the column in the matrix one by one and it desen't produce the expected results - maybe Microsoft will address that issue - conditional formatting is different between table and matrix)

You don't have to spend time on this issue - you can close it - you see we can't even close an issue without mark it as solution 

Thank you 

Oded Dror

[MFelix]

Hi @Anonymous ,

If you are abble to share a sample file I can take a look at it, because since the calculation is based on a measure the context can change the result.

If you have sensative data please share it in private message.

Regards,

MFelix

[Anonymous]

Hi there,

I realize the calculation % (Actual Vs Budget) is wrong thats cause the formating to be wrong i need to calculate %
based on condition of th ematrix then apply the conditional formating to it. Let me work on that and get back to you if I need more help.

Thanks,

Oded Dror

[Anonymous · Accepted solution]

Hi there,

This one works and I did conditional formatting based on that

Formatting =

VAR Percentage = [Actual Vs Budget]

VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||

    Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))

RETURN

If(CheckExpand = TRUE(),

   Percentage,0)

Thanks

Oded Dror

[MFelix · Accepted solution]

Hi @Anonymous ,

Glad I could give some pointers to get the solution don't forget to mark your result as the answer for this post to help others.

Regards,

MFelix