cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bryanc78
Helper IV
Helper IV

Matrix conditional formatting - by row and not the entire table?

I currently have a matrix with 20 rows of sale items.  Columns are dates and values are percentages of items sold by that categoroy per month.  I would like conditional formatting by row to see if the percentages increase or decrease but PBI is applying the formatting to the entire table instead of by row.

 

As you can see below, Orange for Jan in the bottom example correctly shows red as it's the lowest number in the Orange row

The top example, shows Orange in Jan as yellow as it's not the lowest number in the entire table

 

bryanc78_0-1616526971055.png

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @bryanc78 ,

 

You present a table with 3 colours do you want a diverging based on the value or do you want only to highlight the biggest one?

 

If it's a diverging then you need to create a measure simialr to the one below:_

Ranking = 
RANKX (
    FILTER (
        ALL ( 'Table'[Item], 'Table'[Month] ),
        'Table'[Item] = SELECTEDVALUE ( 'Table'[Item] )
    ),
    CALCULATE ( SUM ( 'Table'[Percentage] )  ),
    ,
    ASC
)

 

This will give you a ranking based on the row and month then use it in the condittional formmating:

MFelix_0-1616671884770.png

 

Two remarks:

  • You can change the SUM by a measure if that is the case of your model
  • If you only need to highlight the MAX and the Minimum it is needed to use a different measure

See PBIX file attach.


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



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @bryanc78 ,

 

You present a table with 3 colours do you want a diverging based on the value or do you want only to highlight the biggest one?

 

If it's a diverging then you need to create a measure simialr to the one below:_

Ranking = 
RANKX (
    FILTER (
        ALL ( 'Table'[Item], 'Table'[Month] ),
        'Table'[Item] = SELECTEDVALUE ( 'Table'[Item] )
    ),
    CALCULATE ( SUM ( 'Table'[Percentage] )  ),
    ,
    ASC
)

 

This will give you a ranking based on the row and month then use it in the condittional formmating:

MFelix_0-1616671884770.png

 

Two remarks:

  • You can change the SUM by a measure if that is the case of your model
  • If you only need to highlight the MAX and the Minimum it is needed to use a different measure

See PBIX file attach.


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



View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors