Forum Discussion
Matrix Background color
when amount is divided by area , i want to color each area different color for amount ( can achieve this in conditinal formatting).
catch is color the whole column (background) even when there is no data exist for a selected period.
9 Replies
Gopichakra First, create a measure that calculates the amount divided by the area. This measure will be used for the conditional formatting.
AmountPerArea =
DIVIDE(
SUM('Table'[Amount]),
SUM('Table'[Area]),
0
)o to the table or matrix visual where you want to apply the formatting.
Click on the dropdown arrow of the column you want to format and select "Conditional formatting" > "Background color".
In the conditional formatting pane, select "Format by" as "Field value".
Choose the measure you created (AmountPerArea) for the "Based on field".To ensure the entire column is colored even when there is no data, you might need to adjust the measure to handle periods with no data. You can use a combination of IF and ISBLANK functions to ensure the measure returns a value even when there is no data.
AmountPerArea =
IF(
ISBLANK(SUM('Table'[Amount])),
0, -- or any default value/color you want to use when there is no data
DIVIDE(
SUM('Table'[Amount]),
SUM('Table'[Area]),
0
)
)In the conditional formatting pane, set the color scale as per your requirement. You can define specific colors for minimum, midpoint, and maximum values.
- AmountPerAreaColor1 =VAR AreaInColumn1 = SELECTEDVALUE('AreaSortTable'[Area])RETURNSWITCH(TRUE(),AreaInColumn1 = "A", "#FFA07A",AreaInColumn1 = "B", "#6495ED",AreaInColumn1 = "C", "#32CD32","#FFFFFF")
This works but you need to add this measure in matrix as values and find a way to hide it . This does not work for row subtotals when you have two fields in rows , any help would be great .
Hi Gopichakra,
Thank you for reaching out to the Microsoft fabric community forum. Thank you bhanu_gautam for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.Output:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.Hi Gopichakra,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Gopichakra,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.Hi Gopichakra,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.
no , coloring should be even for all rows (the whole column)
Hi Gopichakra,
I'm not clear on your requirement so please provide the sample data with expected output.
Thank you.
