Power BI - Tracking Matrices

[AliGW]

I am very much a novice with Power BI and am just beginning to get my head around its functionality. In my job, I am tasked with providing analysis trackers: currently we do this in Excel using formulae and conditional formatting (I am attaching one simple sample sheet, but we have many in similar format). Can this be replicated in a Power BI dashboard? I am asking about feasibility at this stage, not the exact steps for this particular example, and will be open to changing the layout to facilitate this, if necessary, as long as this rough format is possible. I also have the data in a normalised format, with one row for each data item (example shown below the tracking matrix in the sample file) for importing into Power BI - will this normalised format allow me to do what I want, or will it need to be altered? Any advice and/or pointers will be very welcome.

Thank you so much!

 

[Ken Puls]

Hey Ali,

So the biggest thing I struggled with in Power BI is getting over the hurdle that it's really not designed for showing good pivot style data. It has a Matrix visual, but I've never liked it, and their conditional formatting is... awful. When you compare it to the polish of what Excel offers, that mechanism for reporting truly leaves something to be desired. If you just want to put out a static report like in your Excel file, there is nothing stopping you from using Power Query to source the data and a Pivot Table to report it the way you're doing. If you're on Office 365 you can even publish that to Power BI and interact with it in Excel online.

Where Power BI shines though, is if you change the thought process and start looking at other visuals. Power BI is meant to be a visual summary that is cross filterable and drill-able. That's the good part about it.

I've added a quick sample that shows both a matrix (pivot) a chart and a slicer. This is obviously VERY rough, but click some boxes in the slicer to see how the drill down ability works.

 

[AliGW]

This is really kind, Ken - thanks. So there would be no way of conditionally formatting the table bottom left? I'm trying to get the interactivity and visuals offered by Power BI rather than using Excel dashboards, for all sorts of reasons I don't need or want to bore you with here! I have already played a bit with Power BI and have Mynda's Power BI training course, which I need to complete, so I'm familiar already with some of the functionality and sliders, but it's getting it all to look the way I envisage it that's a challenge at the moment. Thanks for the attachment - it's helpful.

 

[Ken Puls]

Hey Ali,

You can... select the Matrix, click the paint roller on the right and there is a conditional formatting option. The options are pretty limited though, and make things pretty gross, pretty quick.

I find that trying to apply just a font colour doesn't really work well (too subtle), and putting a background colour on (like I've done here) is akin to an assault on your eyes. I wish we had icon sets, but you'd have to reach to a custom visual to find those (if one exists that meets your fancy.)

The conditional formatting has data bars too, again they just aren't quite as slick as the Excel ones.

 

[AliGW]

Thanks, Ken - I’ll have a look at this in the morning.

 

[AliGW]

This is looking really hopeful, Ken - thank you so much for the 'push' in the right direction. I am going to have a play with some real data today and see if I can get something that will work for us as a starting point.

 

[AliGW]

I have finally got round to following this up, and it's great. Just one thing, though: how can I change the row subtotals to show the difference between the CG and the average of the two? I can't quite work out where it is getting the calculation from. Thanks!

 

[AliGW]

Well, I seem to have sorted this out. I had to introduce calculated columns into the underlying data to get it to work - not ideal, but it will do for now. If you have any other ideas, I'd be happy to hear them.