cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## dynamic conditinal formatting based on mtrix condition

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

13 REPLIES 13
Super User
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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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
Not applicable

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
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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
Not applicable

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
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors