Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VEG_Admin
Frequent Visitor

Conditional formatting multiple columns in matrix separately

I have a matrix with multiple columns, and I'd like the conditional formatting to apply to each column separately, instead of to the entire matrix.  The current result I have formats the background red to green, but since one column has a large number in it that one value is red and the entirety of the rest of the matrix, not just the column with the large number, is green.  Is this possible?

 

1 ACCEPTED SOLUTION

hi, @VEG_Admin 

Sorry for my careless, you could try this formula for [$/SF/CostCode]

$/SF/CostCode = RANKX(ALLSELECTED('Buildings'[Building Name]),[$/SF],,ASC)

Result:

4.JPG

 

By the way, in the 4th column, these three 0.01 values are different value indeed, please put four decimal places for [

$/SF

].

5.JPG

 

 

Best Regards,

Lin

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

View solution in original post

11 REPLIES 11
TomMartens
Super User
Super User

Hey,

 

I'm not sure if totally understand your requirement, but I think what you are looking for can be achieved following this article https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-formatting-by-f... (conditional formatting by field value).

 

If this does not provide what you are looking for, please consider to alaborate more on the issue and also consider to prepare a pbix/xlsx file that contains sample data but represents your data model and upload the the file(s) to onedrive or dropbox and share the link.

 

Hopefully this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom,

 

Thanks for the quick reply.  Sorry my previous post wasn't clear.  Here is the result I'm getting:

 Wrong format.png

This is a matrix where Building is the Rows field, Cost code (1,2,3,4) is the Columns field, and $/SF is the Values field.  I am trying to see where there is a delta between buildings for each cost code, however, the large values in cost code 4 are skewing the entire matrix. so you don't see that there is a "relatively" large delta in cost code 1.  I'd like cost code 1 to look like this:

Right format.png

But to be included in the matrix as a whole, so that each column is formatted based on the range of values in that column, not the range of values in the entire matrix.

 

We don't have a non-secure share option, and I don't see a way to load the file on this site, but here is my sample data:

Sample data.png

Thanks for the assistance.  It is really appreciated.

HI, @VEG_Admin 

Just try this way as below:

Step1:

Create a measure

Measure for formatting = CALCULATE(SUM('Table'[$/SF]),ALLSELECTED('Table'[Cost Code]))

Step2:

When creating Conditional formatting, choose this measure for "Based on field"

3.JPG

Result:

4.JPG5.JPG

 

Best Regards,

Lin

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

Lin,

 

Thank you for the reply.  That didn't do it, I'm thinking its becuase I've oversimplified my model.  There are actually three tables involved, one with the Buildings, one with the chart of accounts, and one with the transactions (GL).  When I tried your method below I get the same result now for every cost code across each building (i.e. it is calculating the total $/sf instead of the $/sf/cost code).  I recreated the model on my personal machine so that I can share it out.  The link is below.  I've left several visuals on there showing the different methods I tried.

 

https://salsallc-my.sharepoint.com/:u:/g/personal/andy_valleyequitygroup_com/Ec_wpTghJm1Im4S_nM4cmfY...

 

Thanks again for all the help.

 

Andy

hi, @VEG_Admin 

You just need to drag [$/SF] measure into Values of matrix but then do conditional formatting based on [$/SF/CostCode] measure

For example:

3.JPG

here is my pbix file, please try it.

 

[$/SF/CostCode] is only used when do conditional formatting, don't drag it into Values.

 

 

Best Regards,

Lin

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

This isn't quite right, the colors are carrying across all the columns for each building, i.e. Building 1200 is always red, regardless of whether it is the most or least expensive per square foot.  I need the red value to be the lowest amount in each column, not just the building with the lowest total.  So 1200 would be red in column B because it is the lowest, but green in column A becuase it is the highest.

hi, @VEG_Admin 

Sorry for my careless, you could try this formula for [$/SF/CostCode]

$/SF/CostCode = RANKX(ALLSELECTED('Buildings'[Building Name]),[$/SF],,ASC)

Result:

4.JPG

 

By the way, in the 4th column, these three 0.01 values are different value indeed, please put four decimal places for [

$/SF

].

5.JPG

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msft 

Thanks for the solution!

In my case - I had a "Sort by column" for the [Building Name], so the ranking didn't work somehow...

Only when I deleted the "Order" column - then the RANKX worked.

So now my "buildings" aren't ordered the way I wanted, but the ranking works.

Any ideas how to overcome this?

FANTASTIC!!! That worked.  I really appreciate you figuring that out for me.  Thank you so much for the help.

hi, @VEG_Admin 

I couldn't download the sample pbix file, please re-upload.

1.JPG

 

Best Regards,

Lin

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors