cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GavinR87
Helper II
Helper II

Matrix contional formatting by column and min/max/average totals

Hi community,

 

I am after a few different things with my matrix table, screenshot below.

 

Firstly I would like to apply conditional formatting, to each column, not the whole table. So each column being a day, I would like to conditionally format the high and low values for each day. When I do it currently it uses the values from the entire table, and formats based on them, not by indivudal column.

 

Secondly I would like to add some rows to the bottom of the table. I would like to add, again for each column, a row that outputs the minimum value for that day, another for the maximum, and another from the average.

 

Is any of this possible? As always grateful for any guidance 🙂

 

yQJiiAR

10 REPLIES 10
lbendlin
Super User III
Super User III

The first part is possible with a trick. You would need to create independent measures for each of the days, and add these measures to the matrix one by one. Then you can do conditional formatting per measure (ie per column)

 

Not pretty (and would require a fixed set of columns), but it would do what you need.

Ah ok thanks!

 

Another one of those frustrating things that is really easy to do in Excel, but struggle to do in PowerBI!

 

 

@GavinR87 it should be pretty straight forward, if you can share pbix file with sample data, I will get you the solution. Cheers!!!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Many thanks, appreciate the offer.

 

I have attached the file and trimmed down the amount of data to keep it small.

 

https://drive.google.com/file/d/1Y1tlxmqX0qLWMMC8z9OiN16lHKcDJ_da/view?usp=sharing 

 

But yeah, really any way to get some form of extra rows at the bottom that show me the min for that day, max for the day, average for the day, etc, would be really great!

 

Cheers

 

PS: Hopefully the Google link works. Not sure what I am doing wrong, but can't seem to see how to attach files here!

@GavinR87 based on the sample data, yellow is min and red is high for each detector in that date range, is this what you are looking for?

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@GavinR87 image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Hi, thanks so much again

 

No not quite, what I am looking for is for each day to be taken sepretely. In my real data I have 20+ detectors, so for each specific day (e.g. 29th May) I want to highlight ones that are either way above or below the average for that specific day.

 

Likewise the min, max, and average flows for a day basis as new rows below the table.

 

So in my example the 29 May would have extra data below, min, max, average, which I would expect min = 31,032 and max to be 43,091 in this example.

 

Likewise the same below every single unique day.

 

Hope this makes sense. Is this possible? 

 

Many thanks 🙂

That's even easier that what I did. Stay tuned






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Thanks very much 🙂

 

Did you manage to see if it was possible to do what I wanted?

Hi community,

 

Does anyone know how I can do these things?

 

Really grateful for any advice!

 

Thanks

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors