cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DECOVIOTI
Helper III
Helper III

Conditional formatting - Matrix by column instead of the entire dataset

Hello Guys

I created a Matrix visual in Powebi which contains on the header "Months" and the row "Customers". I am just counting the number of items per customer.

I am trying to figure out if there is a way to use conditional formatting to go on the basis of the column, instead of  the entire of the matrix.

Such as when I use the Lowest value to Highest color scale, it will compare the values of the whole table, when I wanna  the conditional formatting to compare lowest to highest of only the values in columns.

Is it possible to do that?

Thanks

6 REPLIES 6
tex628
Super User
Super User

You need to create another measure to do your conditional formatting on.

if you have a table like this: 

 USASPAINSWEDENDENMARK
Jan10201010
Feb20193191
Mar30203531
Apr50301242


Create a measure that calculates the share of the individual row i relation to the country:

Measure = 
SUM('Table'[Amount]') / CALCULATE(SUM('Table'[Amount]) ; ALL('Calendar'))

 
Then you use that measure as the 'Based on field' on your conditional formatting:

image.png


Connect on LinkedIn

I dont have a table like that


@tex628 wrote:

You need to create another measure to do your conditional formatting on.

if you have a table like this: 

 USASPAINSWEDENDENMARK
Jan10201010
Feb20193191
Mar30203531
Apr50301242


Create a measure that calculates the share of the individual row i relation to the country:

Measure = 
SUM('Table'[Amount]') / CALCULATE(SUM('Table'[Amount]) ; ALL('Calendar'))

 
Then you use that measure as the 'Based on field' on your conditional formatting:

image.png



@tex628 wrote:

You need to create another measure to do your conditional formatting on.

if you have a table like this: 

 USASPAINSWEDENDENMARK
Jan10201010
Feb20193191
Mar30203531
Apr50301242


Create a measure that calculates the share of the individual row i relation to the country:

Measure = 
SUM('Table'[Amount]') / CALCULATE(SUM('Table'[Amount]) ; ALL('Calendar'))

 
Then you use that measure as the 'Based on field' on your conditional formatting:

image.png



In my case 
Month comes from Calendar table and customer comes from customers table 

The values i have a measure called Items received. Even so it possible to achieve your first table based on the my current situation ?

In that case it should just be the opposite, replace the filter on calendar with customer:

Measure = 
SUM('Table'[Amount]') / CALCULATE(SUM('Table'[Amount]) ; ALL('Customer'))


Let me know if it works! 


Connect on LinkedIn
Anonymous
Not applicable

Give us a graphic representation to get your point...is still quite open your question.... pictures and exel examples of what you have and how would be your expected result.

 

 

Hello 

Thanks for your help

Please see below what i m trying to achieve

 

I have the table below in powerbi and i m using the  the conditional formating  in a Matrix visual which contains on the header "Months" and the row "Customers"

I m using the Lowest value to Highest color scale.

 

What do i need to do?

I would like to put a conditional formatting for each month as per the conditional column. 

Currently, conditional formating in PowerBi works by appling to a range of values in a single column, when you create a Matrix visual using fields such as "MonthYear", all values come from this original single column, thus conditional formatting goes on the entire Matrix.

 

 

pic.png

 

 

 

Hi @DECOVIOTI ,

Actually, the Conditional formatting in Matrix is based on the column rather than whole dataset, in your scenario, for each month, it will sort data differently.

Best Regards,

Teige

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors