March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
Hi there,
This one works and I did conditional formatting based on that
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi there,
this is my calculation - no error there but when I put this measure in my marrix I'm getting this error (see below)
Hi there,
I modify the measure and got it to work but it still showing flags when th ematrix is expandad
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, 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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Hi there,
This one works and I did conditional formatting based on that
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
84 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |