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
Daniel_Fdrvc
Helper I
Helper I

Color only highest and lowest values

Hi all,

 

I‘m strugling with column coloring. Every column in this table below is a simple measure, which differs only in date:

 

AVG_2022-01_Total_amount = CALCULATE(AVERAGE(DB[Month_total_amount]),DATESBETWEEN(DB[Date],"2022-01-01","2022-01-31"))

 

image001.png

 

And now i need to color every column with two colors: only highest value – green, only lowest value – red, everything else - white.

Conditional formating provided by Power BI colors all values and there is no simple build-in solution to color highest and lowest values like in excel.

I have founded different solutions (additional measure for colorig), but can‘t customize them to suit my needs.

If any one can provide some customized solution it will save my day. Write if i need to provide some additonal information about that table and calculation within.

And i didn‘t understand do i will need to create additional coloring measure for each that calculation measure?

1 ACCEPTED SOLUTION

Hi @Daniel_Fdrvc 

 

Below is the output you'll get by applying the steps in the PBIX attached:

 

TheoC_0-1646083988155.png

You need to create the following two measures:

 

Avg = 

VAR _1 = CALCULATE ( AVERAGE ('Table'[Amount] ) , DATESMTD ('Table'[Date] ) )

RETURN

_1

 

Background Avg = 

VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )

RETURN

SWITCH ( TRUE() , [Avg] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )

If you want the Avg by Month, as per Jan and Feb in the output snapshot, then you will need to add the following measures:

 

Avg Feb = 

VAR _1 = CALCULATE ( [Avg] , FILTER ('Table' , 'Table'[Date].[MonthNo] = 2 ) )

RETURN

_1

In the above, you can see that the MonthNo 2 is February.  If you want it for January, just change the 2 to a 1.

Background Avg Feb = 

VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )

RETURN

SWITCH ( TRUE() , [Avg Feb] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )

And the above measure, you can adjust to whatever months you want. Just change the [Avg Feb] to [Avg Jan], etc.  

 

The other item to note is that the SWITCH TRUE gives you Green, Pink, and blank.  If you want other colours, just write them in.  For example, "SWITCH ( TRUE() , [Avg Feb] = _1 , "Black" , 'Table'[Avg Feb] = _2 , "Purple" , "Red" )" will give you Black background for the Avg Feb minimum, Purple for the maximum average, and red for everything else.

 

Hope this helps mate!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

3 REPLIES 3
TheoC
Super User
Super User

Hi @Daniel_Fdrvc 

 

You should be able to achieve the output you are after using a measure and Rules in the Conditional Formatting menu. Here is a link to another post that hopefully assists: https://community.powerbi.com/t5/Desktop/Conditional-formatting-with-dynamic-min-and-max/m-p/599213

 

Hope this helps.

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

That's the problem - how to achieve correct MIN and MAX values from this measure:

AVG_2022-01_Total_amount = CALCULATE(AVERAGE(DB[Month_total_amount]),DATESBETWEEN(DB[Date],"2022-01-01","2022-01-31"))

 when this measure is putted in to a table and average value is calculated for a dynamic count of sales branches, f.e.:

 

Sales branchThat measure (avg)highest value of that measure (1)
1540
2340
3651

Hi @Daniel_Fdrvc 

 

Below is the output you'll get by applying the steps in the PBIX attached:

 

TheoC_0-1646083988155.png

You need to create the following two measures:

 

Avg = 

VAR _1 = CALCULATE ( AVERAGE ('Table'[Amount] ) , DATESMTD ('Table'[Date] ) )

RETURN

_1

 

Background Avg = 

VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )

RETURN

SWITCH ( TRUE() , [Avg] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )

If you want the Avg by Month, as per Jan and Feb in the output snapshot, then you will need to add the following measures:

 

Avg Feb = 

VAR _1 = CALCULATE ( [Avg] , FILTER ('Table' , 'Table'[Date].[MonthNo] = 2 ) )

RETURN

_1

In the above, you can see that the MonthNo 2 is February.  If you want it for January, just change the 2 to a 1.

Background Avg Feb = 

VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )

RETURN

SWITCH ( TRUE() , [Avg Feb] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )

And the above measure, you can adjust to whatever months you want. Just change the [Avg Feb] to [Avg Jan], etc.  

 

The other item to note is that the SWITCH TRUE gives you Green, Pink, and blank.  If you want other colours, just write them in.  For example, "SWITCH ( TRUE() , [Avg Feb] = _1 , "Black" , 'Table'[Avg Feb] = _2 , "Purple" , "Red" )" will give you Black background for the Avg Feb minimum, Purple for the maximum average, and red for everything else.

 

Hope this helps mate!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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