Conditional Formatting Matrix Table - Reflecting Percentages within Customer Territories

[avp115]

I have a matrix table that I'm looking to organize with conditional formatting (that way customers stand out for the right category). In this case, I want to show that customers in Fort Myers Area have greater likelihood of being in the wealthier category, or that customers in Sarasota have lower likelihood of being in the lowest income bracket, etc.

Currently, it's being organized by a gradient based on distinct count of cust_acct_num, so I'm seeing the more populous market areas highlighted, whereas what I want to highlight would be at a row-by-row level.

I've exhausted a number of possible solutions. My best guess would be to calculate an index based on the proportions I'm using for conditional formatting, like so:

Household Income Range Broward Fort Myers Area Melbourne/Daytona Beach Miami-Dade Other Sarasota Area WPB-Ft Pierce Area

Less than $25k	1.06	0.70	1.07	1.30	0.93	0.72	0.95
$25k to less than $50k	1.04	0.84	1.17	1.07	0.92	1.10	1.15
$50k to less than $75k	1.03	0.87	1.16	1.08	0.98	1.17	0.97
$75k to less than $100k	1.01	0.93	1.08	0.93	1.01	1.01	1.00
$100k to less than $150k	1.06	0.92	1.01	0.90	1.14	1.00	1.01
$150k to less than $250k	1.01	0.97	0.89	0.90	1.11	1.15	1.09
$250k+	0.88	1.25	0.77	0.93	0.97	1.09	1.16

I'm not really sure how to do that with measures though! Please let me know if you have any solutions or work-arounds.

[Anonymous · Accepted solution]

Hi  @avp115 ,

Do you want to do Conditional Format by following the value of measure (each value/grouped column total)?

Not quite sure about the logic of calculating Column total, you can create a meausure:

DIVIDE(
    MAX('Table'[Value]), Column total)

Then by selecting [meausre] in the background color

If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the reply from @FreemanZ , please allow me to provide another insight: 
Hi  @avp115 ,

Do you mean that the values of the matrix are color graded by Column.

Here are the steps you can follow：

1. In Power Query – Copy Table – Deleting other columns.

Remove Duplicates – [Group]

Add Column – Index Column – From 1.

2. Joining two tables.

Set the Backgroup color to the following:

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[avp115]

Hello, thank you Liu. The index table is to be calculated using measures (or a combined singular measure).

You will notice for West Palm Beach, for example, that I have calculated 116% based on 12% / 10% (ignoring rounding errors), or for Melbourne/Daytona Beach, 77% comes from 8% / 10% (column total within DMA divided by column total among all). I'm not sure how to configure this matrix using calculations in PowerBI. I can do so in excel rather easily by referencing the right cells.

Again, my goal is to create a gradient conditional formatting for displaying the original data, except having the higher index values show blue, with light gray for lower indexed values.

[FreemanZ]

hi @avp115 ,

try to apply conditional formatting for the background with a measure like:

measure =

SWITCH(

    MAX( tablename[area]),

    "Fort Myers", "Red",

    "Sarasota", "Green"  

)

for more info:

https://community.fabric.microsoft.com/t5/Community-Blog/Conditional-Formatting-Using-Custom-Measure...

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting