Power BI Visual Design Practices

Do YOU Want To Be A Report Superstar?

Hello again P3 nation, today I’d like to drop some reporting knowledge. I’m going to share some of my best practices for Power BI Reporting I’ve developed over the years. As many of you are aware, a large part of the BI developer’s / analyst’s job is to not only create the report, but also to make sure it looks good, tells a story, pops, or my personal favorite “is aesthetically pleasing”.

I decided to sit down and come up with a list of techniques that I use in EVERY Power BI report I create. I want to share this list back with you, our community. While obviously subjective, I think…(hell, I KNOW) you’ll find some of these these useful in adding some finishing touches to help bridge the gap between a good report…and a GREAT report.

Power BI Visual Design Practices Can You Spot ALL The Differences?

I’m going to use this opportunity to shamelessly direct you to a previous post of mine (and Rob’s), DAX “Reanimator” Series, Episode 1: Dynamic TopN… I’ll be using the report from that post to showcase the best practices I like to apply to ALL my reports.

That previous post talks about some pretty cool techniques on how to utilize disconnected slicers. Specifically how to create Dynamic Top N slicers for tables, and how to select a value by which to filter with. If you haven’t already I’d recommend checking it out.

I’ve captured the report in two screenshots below, a before and after if you will (if Power BI Makeover could be a reality show, this would be it). Anyone remember those Spot The Difference sections in the back of the Magazines growing up? Well this is like that, except way cooler! I’ll go ahead and say that you’ll be able to spot the first four differences fairly easily. If you can find all FIVE differences however you’ll get…well nothing really. But hey, you can feel accomplished because that last one is hard to spot! Power BI Visual Design Practices

Customer Sales Report BEFORE Applied Changes:

Power BI.com Client Sales Dashboard Before Techniques

Customer Sales Report AFTER Applied Changes:

Power BI.com Client Sales Dashboard After Techniques

Did you get all five? Ok ok review time, I know most of you didn’t come here to play iSpy for adults. The five best practices I implement in every report are as follows:

  1. Company logo
  2. Lines to help divide sections of the report
  3. Data timestamp to show how recent the refresh was
  4. Report formatting
  5. Dedicated DAX measures table (BONUS POINTS if you found this one!)

 

Let’s take a look at the highlighted examples below and see what changes I implemented between the before and after photos:

Power BI.com Client Sales Dashboard After Techniques

A Little Design Magic Goes A Long Ways

These are all changes that (individually) only add minor improvements to a report, but add them together and it makes a noticeable difference! At the end of the day the customer will notice, and I look for any opportunity to make my reports stand out among the crowd. So let me run you through how I did each in step.

Power BI Design Practice #1) Company Logo

Power BI Visual Design Practices

This one is a quick addition to your report and also helps you set the color tone for other objects. Under the Home tab in Power BI Desktop you’ll find an image icon that will let you add an image from a file on your computer. My personal preference is to add it to the upper left corner of the screen next to the report title.

Power BI Design Practice #2) Divider Lines

Power BI Visual Design Practices

I’m ashamed to say I found ZERO use for the shape button the first year I was developing Power BI reports… One day however, I came across a report that utilized this line shape to create clear separation markers for various parts of the report.

After I saw the line in use I was sold on the concept, I had seen the light and have been using it ever since! I also go the extra mile and typically color it something that’s complimentary to the colors in the company logo. The button to add this is located right next to the image button (previous example).

Power BI Design Practice 3) Data Timestamp

PowerBI.com Data Refresh Indicator Visualization

I started adding this into all my reports because I noticed a pattern of report users who kept asking me if or when the report was refreshed. So I eventually figured I should have a way of providing some sort of indicator that told the end user how recent the data was. There’s a few variations to this depending on which DATE field they want you to use, but they basically all use the same simple DAX measure, and then I place the measure into the multi-row card visualization.

Don’t worry, I’ll provide the measure below. I’ll also run though a few formatting tricks I like to apply to this card. My personal preference is to set the bar color to something complimentary to the logo (noticing a theme yet?), center the title (did I just assume it’s alignment?), and change the font color from the default grey (it’s hard to read!) to black. Smile

DAX Formula (see I told you it was simple):

DAX Most Recent Date Query

Applying a little bit of formatting polish:

PowerBI.com Multi-Card Visualization Formatting

Power BI Design Practice 4) Report Formatting

PowerBI.com Visualization Color and Design

This is less of a single applied step as it is multiple formatting practices applied throughout the report. I’ve already hit on this subject a little bit in the two previous Power BI visual design practices in regards to using complimentary colors. The two key takeaways in this section are object formatting and color coordination.

Of all my best practices I’m showcasing here I’d say this one is the most subjective. However I think that maintaining complimentary colors goes a long ways to creating a professional looking report. I also have a strong dislike for the default title design for visualizations in Power BI. By default it is left aligned and a grey color (AGAIN…hard to read!). I center that sucker and color the background. An added benefit to coloring the title background is it actually forces me to make sure my objects are aligned, otherwise it is VERY noticeable now if they aren’t.

Last But Certainly Not Least!

Power BI Design Practice 5) Formulas Table

PowerBI.com Formula Table

By far one of my favorite hidden techniques of Power BI (AND EXCEL) is to create a dedicated table(s) to store all of my DAX measures. This could be one (or multiple) tables depending on how many measures you have, and how you want to organize them. The ingenious thing too is if you have an empty table, hide the columns, and only have your DAX measures showing in the report view…then Power BI does something MAGICAL.

It will change the icon from a table to the DAX Calculator symbol and put that it at the TOP of your fields list on the right side (regardless of alphabetical order). HOW COOL IS THAT?? Well I geeked out when I discovered this… In case you’re worried that this is complicated to create, it’s not.

Power BI makes the first step INCREDIBLY easy. Unlike Power Query in Excel, Power BI Desktop has a button that lets you create manual tables in your model with a couple clicks and a few keystrokes.

  1. Simply click the Enter Data button on the home tab, name your column & table, hit load, and boom…it’s in your data model.
  2. When you first add this to your model it’ll have the normal table icon.
  3. Assign all your DAX formulas from other tables to this one. Do this by using the Home Table button found under the modeling tab.
  4. Move all all your DAX measures to your new formulas table.
  5. Once you’ve finished re-assigning all your DAX measures to this table, simply right click on the original formula column from that table, select Hide, then save and close your workbook. This last step is required to see the the table icon change, and for the table to move to the top of the field list. Don’t ask me why closing/re-opening is needed, I wish I knew. Eye rolling smile

Step 1 – Create The Formula Table:

PowerBI.com Enter Data Step

Step 2 – Newly created formula table:

PowerBI.com Formula Table

Step 3 – Assigning DAX formulas to the table:

PBI.com Modeling Tab Home Table

Step 4 – Assign DAX formulas to the table:

PBI.com Formula Table

Step 5 – Hide the table column:

PowerBI.com Hide Column From Report View

Before I go though, let me tell you why having a formulas table is so AMAZING.

First off it puts your DAX measures front and center (technically the top?), without needing to dig through all the various tables to find them.

Secondly it forces you to use correct procedure when writing DAX measures. Since keeping measures in this table will require you to reference both the table and column name whenever providing a column reference in a measure. This should be a practice you’re already in the habit of, but if it isn’t then this will force you into it! There’s more details on why this is an important practice in Rob’s post on Five Common Mistakes Made By Self-Taught DAX Students.

Lastly, if for any reason you end up needing to delete a table from your model, the DAX measures won’t get deleted with it since they aren’t attached to the source tables anymore.

There you have it folks, my best Power BI visual design practices. I hope these add as much value to your reports as they have for mine. As always, until next time readers!

EDIT:

So Marco Russo has eloquently mentioned in the comments that there are two issues (currently as of June 2017) when creating a measures table.

First issue is that Q&A does not recognize relationships between measures and attributes in unrelated tables.

Second issue relates to using Analyze in Excel for Power BI. The drill through feature in Excel produces a blank table, instead of the expected data as a result.

If NEITHER of these features are important in your model, then I still stand by the above benefits of a Measures table. Hopefully the Power BI team will continue to make improvements and eventually these issues will go away with a future update.

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro. His passion is collaborating with individuals and organizations to help them understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around Business Intelligence, Reporting, & Design. 

This Post Has 28 Comments

  1. Hi Reid, Great post – thank you! A trick I use is to create a page background file using a screenshot of a PowerPoint slide containing the logo, relevant lines/dividers, (C) etc. that I want on each page. I can then use this file for all pages in my report. This minimizes visual objects that can be inadvertently moved or selected. I can also place a red DRAFT or NOT FOR PRODUCTION USE label on the slide as well – removing it when the report goes into production.

    1. I do the same trick, but I like to go to File>Save As in PowerPoint and select PNG as my file type. I have found that I get better image resolution that way.

      Thanks for the post, Reid!

  2. Reid, someone told me if you use a measures table, the Q&A doesn’t work properly. Have you tested this and do you know if it is true? As I’m sure you are aware, sometimes in the PowerBI world tings are true one day and then not true the next.

    1. Matt that’s a great question! I’ve only actually worked on a few projects where Q&A has been demanded as an essential feature. However those that I have (at the time) had a measures table and seemed to work without “much” issue.

      1. There are two issues using a measure table. The first is that Q&A does not recognize the relationships between the measures and the other attributes in unrelated tables. In practice, it is hard to query the model slicing data trough questions in English. The second issue is that if you use Analyze in Excel, the drillthrough feature is not available (you always get a blank table as a result). For these reasons I suggest to not use a measure table in a model. We asked many times to MS to decouple the physical structure (tables) from the logical view (entities). Hopefully, sooner or later we’ll see something…

        1. This actually merits an edit to the post then pointing out these two things. If you don’t require either of these as a feature I think there can definitely still be a benefit to using a measures table. However it will be good to point out those limitations in the actual post though, and throw you due credit as well. Thanks Marco. 🙂

        2. There’s a third issue too – if you’re using DirectQuery you can’t create a table like this. You might be able to hack around it by bringing in a table of zero records from the source database, but it’s not as straightforward as with imported data.

  3. Nice post Reid! I too always put a Last Updated measure in my reports.

    Something you may want to consider though is the following:

    [Last Updated On] =
    CALCULATE(
    FORMAT( MAX( Sales[OrderDate] ), “mmmm, dd, yyyy” ),
    ALL( Sales )
    )

    This removes any filter context that comes up when the user starts cross-filtering. For instance, the last sale date may be Oct 19, 2006 for the HAT category, even though the overall last sale date is Nov 30, 2006. Seeing that timestamp change when different elements are clicked can sometimes be confusing.

    1. Hi Chris,

      Great suggestion. You can definitely edit the measure to never filter. Some cases it can be handy to see the last sales date for the filter context, so I typically go and edit/remove relationships when I don’t want other slicers / visuals in Power BI to filter this card. That way if I want to use it somewhere else AND still be filtered, I can do both with the single measure.

    1. Completely agree with Koen. I actually prefer white space (or negative space) to lines, they are more easier on the eyes and improves readability.

      1. Hi Jason / Koen,

        Great counterpoint. Like most of these it’s definitely completely subjective. About 80% of the time I use these the consumer likes them. Typically teams like accounting / finance often don’t like any extra visuals. Where product or sales teams love the flair.

    2. Totally agree…you don’t need the lines in #2. In fact I’d say the white space is better and more in line with visual design these days.

  4. Hi, Reid. I like your tips # 3 and 5. Your report looks very clean. But I have to disagree with #1, #2, and #4. Putting your logo in the top left seems like a poor use of prime screen real estate. The only reasons I would consider including a logo in the top left is if it added some extra information. For instance, you have reports for multiple companies, or the report is sent to customers and they need to easily identify it is from your company. Otherwise it’s just decoration. I try to reserve the top left for the most import things and the things I need to know first before taking in information from a report. I agree with Jason and Koen on the lines – I prefer to use whitespace for separation. When I do use lines, I prefer to use light gray so it doesn’t draw to much attention but does its job. I’m not a fan of using (comparatively) bright colors in my chart titles like you have done. It causes reports to fail the squint test – if i squint at my report what stands out should be important information. In your before version, the bar chart and the funnel chart stood out. In the after version, it’s just chart titles – they actually draw my eye away from the data/charts. That said, if my clients made Power BI reports of this quality, I would have much less training to do. 🙂

    1. Hi Meagan, great points. Subjective posts like this definitely generate a lot of conversation, which I love. Projects that I’ve worked on personally often get requests for a company logo and title because these are often used as a screenshots in Power Points. However I can tell you that reports I’ve done for Finance teams look a lot closer to the before image. The clientele is the #1 determinant of whether or not they’ll want them. But since I come from a graphics background, I enjoy a bit of flair in the report. One technique I like to do for those people who don’t want solid colors in their chart/graph titles, is to keep it white, and instead then color the font that color instead. Still adds a tiny bit of that color theme but makes it less obvious. Glad you found a few of the tips useful, as long as at least 1 was helpful then I consider the post a success. 🙂

      1. Thanks for the response, Reid. I enjoy discussing data viz as well. I agree that the design should cater to the intended audience. And to clarify, I’m not so much against any logos as I am the placement of the logo. We are all busy and distracted so I try to maximize the reward to effort ratio for report users while optimizing (if not minimizing) cognitive load (if you haven’t read Andy Kirk’s book, I recommend it: http://www.visualisingdata.com/book/). Everything you put on the page adds cognitive load for the user. And for me, reward includes both the desired information as well as a pleasant user experience. I think this is where your “flair” comes in. I agree that adding images can improve the user experience. But I try to use them to add information, which can be tricky (and quite subjective). It’s interesting that you come from a graphic design background. Some of my friends found it a bit challenging to transition from graphics to data viz, simply because the approaches and goals are rather different. I’d love to read about your move into data viz and what ideas/books/blogs you like/agree with, maybe as a blog somewhere?

  5. The need for format and style depends, in large part, on who your audience is. The line manager doesn’t care as much, if at all, about logos, lines, etc. The C class executive DOES care about these things. They have been brought up on Power Point, where everything is nicely formatted. This is normally what they want to see, and to show others.

    1. 100% agree Mike. Depending on the team, the manager, or the individual, formatting is entirely optional, and sometimes NO formatting is requested. Hence the reason I prefaced that section with the subjective disclaimer. I come from a graphics background so I like a bit of flair. However at the end of the day, go with the client needs. 🙂

  6. Formulas Table, what a great idea. I started using the concept right after I read the article, even changed a couple of models I am already using.
    Today I’m building a model from scratch, so very heavy in developer mode, and as usual my mind and my DAX code tends to make Y split choices. Should I think it this way or that way, so I do both! An hour or so later and its clear one path was the wrong way. I put all the measures I made on two tables TestCalcs1 & TestCalcs2. I was so much eaiser to clean up. I delete the entire TestCalcs1 table and moved the good calcs to my “production” Formulas table. Plus the icon change kind of makes me smile too! At one time all three of my formulas tables were at the top of the list.

  7. Hi Reid,
    Another note on the formula’s table (I use them too, but I call it “Metrics”),
    When we were taking a look at my Simpsons submission Pat showed me that my disconnected metrics table will also break the upcoming quick insights (e.g. right click->”explain the increase”) feature. I believe this is for the same reason Marco suggested that the engine cannot determine the relationships between disconnected tables. Pat told me its technically possible for them to modify engine to work it out but its would be a ton more work to implement it… so it means it will be broken for some time I’m thinking… So also keep this in mind for models that you want to use quick insights on… it’s so much cleaner so I was bummed to see that.

  8. Question on use of formulas table in Excel 2016 – I have a fact table coming from Power Query and then various Measures tables (Revenue measures, Costs, Volume) – sometimes when I refresh just the fact file, the values in my pivot don’t update until I interact with the pivot table. Is it necessary to refresh the measures tables to force recalculation?

    1. Hi Chris,

      Great question! Refreshing the measures table wouldn’t affect that. During a “refresh all” process in Excel it first refreshes the connections / tables in the data model, then loads all that new data back out to the DAX measures in the various tables, charts, graphs, slicers, etc… Normally a full / complete refresh pushes all the new data out but sometimes you get scenarios like you’re describing where a PivotTable is still used the cached old data from the data model. Wish I could provide a clear answer on WHY this happens but sometimes it’s just a glitch that I can’t always explain when it happens to me. Hope this makes sense?

Leave a Comment or Question