Results 1 to 8 of 8

Thread: Power BI - Tracking Matrices

  1. #1
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription

    Power BI - Tracking Matrices



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    Thanks, Ken - I’ll have a look at this in the morning.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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!
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •