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

[GavinR87]

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 🙂

[lbendlin]

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.

[GavinR87]

Ah ok thanks!

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

[parry2k]

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

[GavinR87]

@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!

[parry2k]

@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?

[parry2k]

@GavinR87 

[GavinR87]

@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 🙂

[parry2k]

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

[GavinR87]

@parry2k 

Thanks very much 🙂

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

[GavinR87]

Hi community,

Does anyone know how I can do these things?

Really grateful for any advice!

Thanks