Making sense of challenging data topics one step at a time.

Category: Visualization Standards

Conditional Formatting with a Column Chart?!

In my article last week, I explored how to compare a goal built off last year’s revenue compared to this year’s actuals. Some of you might be thinking that system worked, but it felt cluttered. I would agree with you. I like using cluster charts in the right scenario, but I think we can do better. A couple of weeks ago, I did an article on why I love using conditional formatting to highlight important values. You also might remember that it was focused on using the table or matrix visual.

A little known fact is that you can leverage conditional formatting in other visuals. Imagine using conditional formatting to quickly identify which months we hit or missed our goal. I thought it would be great to dig into some options to really make your column charts pop!

Using Some Conditional Formatting Magic

A few weeks ago, I wrote an article on how to do perform field based conditional formatting. The same principle applies to building out our formatting in our charts. I start by building a measure that compares current year revenue to our goal.

CY Revenue Color = 
    SWITCH(TRUE(),
        [CY Revenue] >= [CY Goal], "Green", 
        [CY Revenue] >= ([CY Goal] * .90), "Yellow",
        "Red")

The formatting will show green if revenue exceeds our goal. We then identify any months that reached 90% of their goal in yellow to highlight that they were close, but did not quite make it. Then any months where we missed our goal, it now shows in red.

Now we need to apply the formatting to our chart. After selecting the visual, we need to go to the visual properties pane. Then you need to expand the columns section and click on the “fx” button. From there, just select the field value formatting style and select the CY Revenue Color measure we just created.

Applying our measure for conditional formatting.

The result is a chart that looks like this:

Field based conditional formatting on a column chart

It was a few extra steps, but it makes it clear we started the year off strong, but have some work to do to get back on track. Amazing what a little color can do!

This Is Great, But…..

I know what you are about to say – “I love the color, but it is hard to see how this compares to the actual goal. Can we do something about it?” Of course we can!

When traditional conditional formatting lets off, we can build our own with a series of measures. Using a stacked column chart and a few measures, we can show our revenue versus the goal to make it easier to see. We need to build four measures to make it easy to track against our goals: Revenue up to our goal, revenue that exceeds our goal, revenue short of our goal, and revenue left to meet our goal. Yes, it seems complicated, but just wait until you see the final product.

Our first measure will calculate the CY Revenue that is up to our designated goal. If we exceed the goal, we will just return the CY Goal instead and represent the remainder of the revenue in another measure.

CY Revenue to Goal = 
IF([CY Revenue] >= [CY Goal], [CY Goal], [CY Revenue])

Next, we want to show anything that is over our goal in another measure. Using a similar formula, we will calculate the difference of CY Revenue and the CY Goal. Note there is no else statement as we are only interested in revenue over our goal.

CY Revenue Over Goal = 
IF([CY Revenue] >= [CY Goal], [CY Revenue] - [CY Goal])

Next, we want to calculate the amount we missed our goal by taking by looking at months were we missed our revenue goal.

CY Revenue Short of Goal = 
IF([CY Revenue] < [CY Goal] && 
     MAX(SalesAggregated[Month]) <= EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

We don’t want our chart to show missed revenue for months that have not happened yet. We also don’t want to show missed revenue for our current month but rather as revenue to go. By using EOMONTH(), I am able to take the end of the month that we are in today and subtract a month so it will only summarize data before my current month.

Now doing something similar, I can calculate the revenue that still has to happen before the end of my current month. We will essentially do the same measure as above but looking forward.

CY Revenue to Go = 
IF(MAX(SalesAggregated[Month]) > EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

And with a little color assigned in the, we can make our chart pop!

Homemade conditional formatting using some measures and custom colors

Which Method When?

Both methods work really well for providing conditional formatting. But one obviously takes a lot more effort to create. And beyond that, I need to make sure my DAX is clean and doesn’t slow down my report.

Why would I go down this route? I find that sometimes you have a stakeholder who really likes to get into the minutiae of the data and wants to be able to accurately see their plan versus actuals. This might have been a little more complicated than required, but I wanted to show you how granular you can get.

From my experience, most people do not want to get this granular with their reports. But in case you run into someone who really wants some additional detail, you now know how to make it happen.

Have you run into something like this before? Have you been asked to add some formatting to your column charts? Are you going to start doing it? Tell me in the comments below!

Mr. Bad Example – Bar and Column Charts Best Practices

I cannot begin to express how often I see bad examples of data visualization. Sometimes it is unintentional but it can have far reaching implications. Not following best practices and visualization standards often misleads report consumers and eventually bad decisions.

Even worse, report creators often manipulate these visuals to get the data to support their narrative. I am all for sharing a narrative with data but I draw the line at manipulation. If you have to twist and manipulate your visuals to tell your story, it is probably time to get a new story.

I am passionate about this topic because I watch people manipulated by these visuals all of the time. Sales presentations, all hands meetings at work, poll results, and even the news media commit these violations. My goal for this article is two fold. The first is to help you understand the why of these best practices for your own reports. The second is to help you identify these violations in the wild and help call them out.

My articles usually focus on Power BI. However, this article applies to any visualization tool you will encounter.

The Importance of Best Practices and Visualization Standards

You might be asking to your self “why is Dominick so passionate about this?” To me, the loss is considerable if you are not careful. Here are some common scenarios where not following best practices can get you into trouble.

Financial Costs

This is the first scenario that often comes to mind when it comes to bad visualizations. We use reports and visualizations to make data driven decisions.

Let’s pretend you own a store that sells widgets. You take a look at a chart that shows your sales doubling month over month. As a shop owner, you might double your order for next month because if the trend continues you want to be prepared. It totally makes sense! You double up your orders (or even triple them) to meet demand.

Column Chart Showing Revenue Doubling Each Month

However, I left something out of this chart. There was no scale and my axis did not start at zero. In fact, I never actually doubled my sales. I only had a 10% increase of sales month over month.

Column Chart Showing the Same Data Above with Proper Scale and Labels

Now you might be thinking that this really is not a big deal as widgets do not have an expiration date. But the implications of this could be considerable as you took cash in hand and put it into inventory. You still have an assets on hand with the widgets, but you will need to sell them to make sure you have cash on hand. This could put you in a bind as an owner as you could potentially become “cash poor” which results in trouble paying bills, making payroll, and even paying yourself.

Someone Could Get Fired

Have you ever created a report that could get someone fired? I have!

One of the most common reports I see are around service level agreements – also known as SLAs. SLAs determine effectiveness of a process or service. Contracts include these metrics that help determine success of services.

Remember when pizza shops had the “30 minutes or its free” guarantee? It went away before I was old enough to buy my own pizza. Likely because someone did an analysis of their metrics and found they were not delivering enough pizzas on time.

If you managed a pizza shop, you might have had an SLA of delivering pizzas in under 30 minutes 97% of the time. That means out of 100 orders, three of them could be free because they did not make it on time. Your district or regional manager likely has a report showing your performance. What if they showed a report that showed a trend where your on time delivery metric dropped significantly. You might be in danger of losing your job for not performing as expected.

But if the chart is only moving from 99% of the time to 98%, it is still above the SLA and overall not too bad. The point here is that visualizations not following best practices can imply someone is not performing well in their job. This leads to someone getting disciplined even if they are performing well in their job.

Your Future Ambitions

People are often looking for ways to grow their career. They put together statistics and figures to show how great they are and why they deserve a raise or promotion. They might also provide some of the same information to help those above them move up the ladder as well. But what do you think the implications are if your visualizations used for this purpose are found to not use best practices?

I see it as a breech of trust. While it may seem insignificant at the moment, the impact could be long term in your career. Every report created could be scrutinized and called into question. It seems crazy but the potential is there. If a decision made from the report costs your organization a considerable amount, you might even lose your job!

This is why I believe it is important to consider best practices whenever creating reports. Leaders must take the time to make sure their teams understand these practices and live them for the benefit of their team.

What Should I Know About Best Practices?

There are 4 best practices that you should be concerned with when you create your reports. Follow these and you will be well on your way to building quality reports.

Best Practices to Follow #1 – Always Start at Zero

This is a non-negotiable standard when it comes to bar and column charts. They must always start at zero to avoid misleading consumers. This is the most common infraction that I see when it comes to data visualizations.

As discussed above, I can show amplified trends by changing the axis to start at a higher value. This also causes confusion and frustration for report consumers.

Column Chart That Starts at Zero

Best Practices to Follow #2 – Label Your Axis

The next most common ignored best practice is labeling your axis. We see the categorical axis getting a label (category, time, etc.) but he value axis does not get the same love.

To avoid confusion, you will want to label your value axis to match the measures in your chart. There is some flexibility with this as you can change the units to make it easier to read. For example, you can set the units to millions to compress the chart and make it easier to read. While you might be distorting the view some, the labels will clearly define the scale and make it easier to understand.

Labeling Your Categorical Axis and Value Axis

I also recommend using some lines to highlight your axis across the chart. I worked with an organization a few years ago that hated those lines. It was forbidden to add them to any chart in a presentation. While I strongly disagreed with this approach, they used data labels which helped keep things in context when viewing a chart.

Best Practices to Follow #3 – Use Data Labels to Highlight Key Values

My friend and colleague Sally Paczosa always used the term “hover to discover”. I stole her line and say it all the time when it comes to reviewing reports in Power BI. If I want to know the actual number, I can hover over a column or bar and see what the value is. It is one of the best features that people forget about when it comes to reporting.

But as much as I love it, I always have to consider my audience for a report. If my consumers are fluent with tools like Power BI, they already know what to do. Without proper training, they might miss that opportunity and never actually do it. In the same vein, I have to consider the venue they will consume the report. They can “hover to discover” all day long at their computer. But what if they are in a town hall meeting looking at a chart being presented? How can they view the actuals?

Data labels allow us to quickly identify key measures on a report and allow us to add context quickly. This prevents the fatigue of trying to match columns to the axis and accelerates understanding.

Using Data Labels on a Column Chart

Best Practices to Follow #4 – Add Lines for Clarity

I like adding lines to help provide better context and clarity to a chart. This is extremely helpful for reports that will be consumed without a talk track behind them. You can highlight common statistical measures such as maximum, minimum, mean, or median. You can even add a constant line if you want to highlight a particular goal. If not used properly, it could clutter your charts. However, properly placed it could make a huge difference.

Adding a Constant Line to Highlight a Goal

Best Practices Apply to Chart Types as Well!

Which is better? A bar chart or column chart? The answer will depend on the context of your data and the intent of your visualization. Here are some thoughts to help you make the right selection

Column Chart vs Bar Chart

People tend to use these terms interchangeably but don’t realize there is a difference between a bar and column chart. In a bar chart, the data is represented horizontally. Column charts represent the data vertically.

Column Chart vs Bar Chart

While it may seem inconsequential to interchangeably use these terms, it is important to really understand the difference to help represent your data.

Column Charts

Column charts can be used for categorical data, but they often shine when it comes to series based data. The natural left to right progression of the canvas allows users to follow a timeline to see trends.

They also shine because the vertical orientation of the values is easier to grasp some statistical measures such as maximum and minimum. It is less cluttered in this orientation and the labels are easier to read.

Bar Charts

Bar charts do an amazing job of showing large amounts of data in a compressed area. If you have long list of category labels, they tend to fit better horizontally. This prevents crowding and confusion.

In addition, they do a great job of allowing users to scroll down for large amounts of data. Naturally, your venue is going to have an impact on this functionality. You cannot just scroll down on a chart if you are in the audience of a town hall meeting. But for reports that are consumable at your desk, it is easy to scroll down and see more information.

Stacked and 100% and Clustered, Oh My!

Now that you have a hint at which direction you want your charts, it is time to pick the right one. If you only have a single series of data, then you could pick a stacked or clustered chart and it would not make a difference. But depending on what you are trying to show, it is important to pick the right chart.

Stacked

Stacked charts show the composite makeup of the whole value. Let’s stay I own a store but recently started selling my products online. I might want to see the breakdown of my revenue from my store versus online. A stacked column chart allows me to quickly identify the makeup of revenue which can help provide context. For example, if I have a month with higher than expected revenue, I might see that online sales were higher than in the past.

Example of a Stacked Column Chart

While this chart style is versatile and useful, it is not always easy to make comparisons. The orientation of the values makes it difficult for one to one comparisons.

100% Stacked

Similar to the stacked chart, 100% stacked charts show data in a similar format. The biggest difference is that the bar or column do not show totals. Instead, it shows the percentage of the composite elements.

Take our store versus online sales question. If you looked at the chart above, you could see the breakdown. But what if I wanted to understand the percentage of my sales that were in my store versus online. I could do some math to figure it out, but it is easier to show in the 100% stacked chart.

Example of a 100% Stacked Bar Chart

This is where the 100% stacked chart shines. It allows us to understand distribution a little easier which gives us a better comparison. We can quickly identify trends of online sales and see if they are increasing, decreasing, or remaining flat over time. The only downside is that you cannot see the overall totals.

Clustered

Clustered charts allow for quick comparisons. Not as precise as the other two charts, it does a good job of allowing you to compare two values side by side. Where most people go wrong is they try to compare categorical data which can get confusing. I like to use it to compare year over year or even month over month data. This allows for a close comparison without a lot of clutter.

Example of a Clustered Column Chart

What If I Am Unsure of Which One to Pick?!

The best part of most tools is that you can quickly change which chart you are using! Try some different ones. Maybe you will need to use a couple of variations to get your data to appear correctly. The goal is to communicate a story. One of my favorite ideas is to show the visualization to someone and have them interpret it for me. Don’t give them any hints – let them tell you. If what they say matches your narrative, then you are likely on the right track!

Next Steps

Now that you have a better understanding of best practices when it comes to bar and column charts, what do you do next? My first recommendation is to go back through your reports and presentations and find out if you were following these best practices. If you weren’t, try to understand why. I find that we tend to take shortcuts to save time when we have a hard deadline coming up. Building in a little extra time in your plan helps ensure nothing gets left out.

What about when we see best practices not being followed? What do we do? I find most people make these mistakes out of ignorance. They might know all of the best practices or how to apply them. I always recommend coaching people and helping them learn. I also find being the best example helps change the behaviors around you. By getting others to follow your lead, you will find others will do the same.

How often do you see these best practices ignored? Do you have any egregious examples? If so, tell me in the comments below!

Conditional Formatting – Moving to the Next Level

Last week, I went through the basics of conditional formatting. I shared how it helps take your reports to the next level and the steps required to use it. We looked at gradient and rule based conditional formatting but did not touch the field option. I wanted to split this one out because it can be a little more complicated but is quite versatile.

What is Field Based Conditional Formatting?

Isn’t all conditional formatting based upon fields? Of course it is! We are always looking at field values to activate conditional formatting. Field based conditional formatting is not looking at the field values for what needs to happen. Instead, it is looking to the field for what formatting is supposed to appear.

Let’s use our example from last week where we looked at revenue versus goals. We used a simple set of rules to highlight table rows as red, yellow, or green based on values.

Enabling Rule Based Conditional Formatting

Instead of setting a bunch of rules, we can create a measure that specifies these colors. If you remember a few weeks ago, I did a post on using the SWITCH() function in DAX. I am going to use it to create a measure to specify the colors I want to use.

Field Formatting v1 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "Red",
        SumRevenue < 1500000, "Yellow",
        SumRevenue >= 1500000, "Green",
        "Gray")

Note that I am using colors by name in my measure. It is not fancy, but it gets the job done. Once the measure is ready, we just need to apply it to the column. I select field as my format type and then select the measure I just created.

Setting Up Field Based Conditional Formatting

The result is my table formatted as expected.

Results from Field Based Conditional Formatting

And just like that we are using a measure to drive our conditional formatting.

This Looks Great! Can I Customize My Colors?

You can absolutely customize your colors! Instead of writing out the words, you can use hex codes to set the color.

Field Formatting v2 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a00",
        SumRevenue < 1500000, "#f7da00",
        SumRevenue >= 1500000, "#006616",
        "Gray")

Using a branding guide to help me, I pulled the red, yellow, and green hex codes to use in my report.

Field Based Conditional Formatting Using Hex Color Codes

But now my colors are a little too dark How do I fix that? Here’s a little pro tip – you can add two numbers on the end of your hex code to determine the transparency of the color. I wanted to soften the colors a bit and make it 50% transparent, so I added a 50 to the end of each hex code.

Field Formatting v3 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a0050",
        SumRevenue < 1500000, "#f7da0050",
        SumRevenue >= 1500000, "#00661650",
        "Gray")

And the difference is dramatic!

This makes a huge difference in our formatting and it is much easier to read.

Why Go Through All Of This Effort For Conditional Formatting?

If you are anything like me, you are probably wondering why you should go through this kind of effort to build out measures for conditional formatting. Every tool has a purpose and this is one that rarely gets leveraged. But consider some of these use cases where you might find it useful.

Complex Rules

When using rules, you find that you are put into a simple set of conditions that drive your formatting. Maybe you have a complex set of conditions that you need to consider. Instead of a simple greater than, less than, or between set of rules, you can build out some complex logic.

For example, you only want the colors to appear for the months in Q4. You cannot accomplish this with the standard set of rules. But with a little extra DAX, you can make that happen.

Field Formatting v4 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(QUARTER(MAX(Revenue[Month])) = 4,
    TRUE(), 
        SWITCH(TRUE(),
            SumRevenue < 1450000, "#9c0a0050",
            SumRevenue < 1500000, "#f7da0050",
            SumRevenue >= 1500000, "#00661650",
            "Gray")
    )

If you wanted to get really crazy, you can build out a complex measure that darkens the color the closer you get to the due date of a goal. The sky is the limit!

Color Flexibility

What if you had a report that can be filtered for multiple customers? You could setup your formatting to highlight values using their primary color color scheme.

If you have a customer table, you can specify some key palette colors and reference them in your SWITCH() statement. This enables a personal touch for each customer without having to create separate report pages or files for each one. Talk about scaling out your reporting!

Anything Else I Need To Know?

Remember, you are building out your conditional formatting using measures. You always want to optimize them to be efficient and not tax your report canvas. You are adding additional calculations to your report canvas, so don’t overdue it!

So how about you? Have you used field based conditional formatting before? If not, do you have a use case? If so, tell me about it in the comments below!

Draw Attention with Conditional Formatting

Conditional Formatting is a powerful element that enhances your reporting. When asked for feedback on a report, I often recommend users use some form of conditional formatting to help draw attention to key elements. I won’t lie to you – it takes extra effort to make it work. However, with a little planning you can make your reports pop while guiding your readers to the most critical information on the canvas.

Why is Conditional Formatting Important?

Conditional formatting takes good reports and makes them great. When you design a report canvas, it can quickly become cluttered with too much information. It overwhelms the viewer and causes anxiety. As a result, energy is wasted on trying to understand what the report says. Conditional formatting helps resolve this issue for the viewer.

Conditional Formatting In Your Car

Think about driving a car and looking at the dashboard. There are several measures and metrics to tell you what is happening. A speedometer tells you how fast you are traveling. A tachometer tells you how many revolutions your engine is making in a minute.

However, some of the gauges have a little help. For example, your fuel gauge tells you how much fuel is in your tank. It is a simple – when the gauge reaches “E” or “0”, it is time to get more fuel. But with the bustle of everyday life, sometimes we forget to check the gauge. That is when the low fuel light comes on and alerts us that we need to stop and fill up. The conditions are simple – when the fuel level reaches a low enough point, the light turns on.

Now, the light is not really necessary. It is not required for your car dashboard to work. Yet, we find it useful as it draws our eyes to a potential concern so we can resolve it. It alerts you to an issue that pairs with an action to resolve it.

The check engine light works in a similar fashion. It alerts you of a problem that you have to address. Unlike the low fuel light, the check engine just tells you there is a problem. You will have to do a little more research to find the issue. If you don’t have access to a scanner, you might need to take your car to a garage to have it checked out. Either way, you know something is wrong and can resolve it before it becomes a bigger problem.

Augmenting Reports with Conditional Formatting

Conditional formatting does not replace your current report visuals. It draws the eyes to key information on the canvas so you can act. With a little splash of color, you can draw eyes quickly to important data points.

Now, you might be asking “don’t I need a legend or something to tell people what they need to know?” Believe it or not, you really don’t need to do much. If you highlight a value in bright red, almost all viewers will understand that it is bad. When you highlight something in green, it is good. That does not take much effort to understand.

The trick is properly apply the conditional formatting so users are able to understand what is on the canvas.

Applying Conditional Formatting

So all of this sound great. But how do I apply it?

I find the easiest place to apply it is with a table or matrix visual. Even though Power BI has lots of rich visuals, many people still hold on to their PivotTables from Excel. They love seeing data in a tabular format. This is a great introduction to how to apply conditional formatting in a standard report.

For this article, we will be looking at a simple table that lists sales revenue by month for 2021. Our goal is to call out values that need attention so our consumers can understand them.

Table of Revenue by Month

Highs and Lows with Gradient Formatting

The table above lists out how much revenue was received per month. What if I asked you which month had the highest revenue. You are pretty smart, so you could figure it out fairly quickly. But what if the table had 5 years of revenue? It would take a considerable amount of brain power to figure it out. You could do it, but it would not be easy. Gradient formatting aides viewers in seeing which months had the highest revenue.

To start, we have to enable conditional formatting on the cell backgrounds. We will simply select the table visual, go to the visual properties, expand cell elements, select the revenue column, and turn on the background color element.

Enabling Conditional Formatting on a Table

You will see that we can easily identify July 2021 as the month with the highest revenue. We also can see February as the month with the lowest revenue. There is no denying it – conditional formatting makes it much easier to read!

But this just shows highs and lows. What if I have a revenue goal that I want to meet. Gradient conditional formatting does not meet that requirement. So how do we take our visualization to the next level?

Thresholds and Goals with Rule-Based Formatting

Instead of highlighting on a gradient scale, we can set formatting on a series of rules. To get started, select the function (fx) button under background color. This will pop open a new window to allow us to make our changes.

From here, we will change the Format Style from Gradient to Rules. After that, I needed to add two more rows of rules to achieve my goal. From there I can establish my rules to highlight which months did not reach my revenue goal of $1.5 million dollars.

The first rule is to show anything that missed my revenue goal. So I will start with zero to $1.45 million. I then assigned the color red to that field. Why not $1.5 million? While that is my goal, I want to highlight any revenue amounts that were close to my goal, but did not quite make it. So my next rule highlights anything between $1.45 and $1.5 million with yellow. And finally, anything over $1.5 meets the goal and is highlighted in green. However, I need a top end value for that rule, so I just put $5 million in there as a place holder.

Setting up Rule-Based Conditional Formatting

With my rules in place, it is much easier to tell which months met or missed the goal of $1.5 million in revenue.

Highlighting Values with Rule-Based Conditional Formatting on a Table

It is easy to see that we hit our revenue goals in eight months of 2021. We missed our goals in February, April, and May. And while we were close, we did not quite reach our goal in June. This formatting draws our eyes and makes it easy to where we missed the mark so we can adjust our plans for 2022.

Can I Format the Entire Line?

The short answer is yes – you can do that. The only catch is that you have to update the rules for each individual column in your table. Just select the Month column and start making your changes.

The biggest thing you need to do is change the field the formatting is based upon. It defaults to the column you are formatting. However, we need to set it based upon the values in the revenue column.

Formatting Conditional Formatting Rules for the Month Column using Revenue

Set the rules to match your original formatting and you will have the entire line formatted.

Conditional Formatting Applied to All Columns in a Table

With all of this in place, I have provided conditional formatting leveraging the background color. This was easy because I only have two columns in this table. If I had a lot of columns, it might be more time consuming to setup.

Now, as nice as background colors are, I am constantly asked if there any other options out there. The answer is of course!

What Else Can I Do with Conditional Formatting?

I like using a cell’s background color but there are other options out there. If you want to use color, but the cell background is too bold, then you can use font color instead.

Conditional Formatting Using Font Colors

One of my personal favorites is to use icons with the report.

Conditional Formatting Using Icons

With icons, you can select different styles that suit your needs. You also have the ability to move the icon to the left or right of the values in the table. And if the occasion calls for it, you can just use an icon in the field instead of a value.

Setting Up Conditional Formatting Using Icons

The other favorite is to leverage data bars. This aligns better with gradient conditional formatting, but helps show magnitude of the values better.

Conditional Formatting Using Data Bars

Regardless of what style of conditional formatting you use, I promise you it will elevate your reports and make them easier to understand.

Next Steps with Conditional Formatting

So with this knowledge of conditional formatting, should I know anything else? Of course – the biggest thing is knowing when to use conditional formatting. Quality report design rules still apply. Overuse of conditional formatting can cause your report canvas to look cluttered and confusing. It also could impact performance as calculations are taking place in the background to enable the formatting. Just remember that less is more.

The other thing you know is that conditional formatting can be applied to more than cells in a table or matrix. In fact, there are other elements of conditional formatting to share. I have an upcoming article that will dig into those features, so keep an eye open for it!

Until then, have you used conditional formatting? Do you have a use case ready to go? If so, tell me in the comments below!

Mobile Reporting That You Can Actually Use!

The February 2022 update of Power BI came with a major change for mobile reporting. It was so exciting that I dropped my original article for this week so I could share this update. Mobility has always been a part of Power BI dashboards, but there were some challenges. It was not flexible and did not offer much with visual interaction.

Mobile reporting was then added to Power BI reporting, but there was still one major challenge. What worked for consumers on a computer or tablet did not work for mobile phones. To make it functional, you might end up creating two different versions of the report to accommodate mobile users.

With the February 2022 update of Power BI, we can finally take an existing report and modify it for a quality mobile experience. This is a huge step forward for the frontline and I cannot wait to use it!

Data on the Frontline

Data gurus pontificate about how leaders should make data driven decisions. I agree with them. I love having the right data to make decisions. With some analysis, I have likely avoided making poor purchases because of my research.

As wonderful as many of these data gurus are with their ideas, there seems to be a prejudice towards frontline leaders. The gurus spend their time reviewing data about what happened and how they can turn their insight analytics to prescriptive analytics. But the frontline workers are hungry for data and welcome it for making decisions.

Moving and Shaking on the Frontline

Frontline leaders spend their day on their feet. They are normally walking the floor pitching in where needed. If they are in retail, sometimes they are running registers while associates are taking breaks. In manufacturing they could be helping to repair an issue with a line so production can resume.

Frontline leaders likely have a desk somewhere in the facility, but they rarely sit at it. Sometimes leaders might need to share a computer at that desk. During my time working retail during college, I worked at a store that had four managers and two supervisors who all shared the same computer. If they wanted to pull a report or review some data, they had to wait in line. If content creators only create reports that must be viewed on a computer, they will never get used.

Decision Making on the Floor is Paramount

Frontline leaders are constantly making decisions when walking the floor. Restock orders, new material requests, and resource management are all made in real time. If you want to enable your user leaders to make data driven decisions, the data needs to be with them on the floor.

Now you might be thinking “Hey Dom! Laptops are portable. They should just carry one with them at all times on the shop floor.” And that is a good point – a laptop in hand is fairly portable. However, there is a risk factor. You need to find a solid surface to set the machine on so you can use it. You also run the risk of dropping it or setting it down and forgetting it.

This is where a mobile phone becomes the star. It is compact, powerful, and convenient. Properly managed, this will enable your frontline leaders to have mobile reporting with them wherever they go. And before you say anything about the risks of dropping or misplacing a laptop are the same for a phone, I will challenge you and say it is not. Ruggedized phone cases are easy to find and people tend to not misplace their phone. Even better, if a company has a bring your own device (BYOD) policy, your leaders can use their own device. They tend to protect it more than company issued hardware and don’t want to be without it.

Know Your Audience

One of the challenges of content creators is understanding the frontline audience. Like many creators, I love putting together flashy reports and combining data for deeper insights. This is great content for consumers who are looking to learn more from the data and have time to pour over reports.

Frontline leaders tend to be more focused on current operations. They want to review data to decide what needs to happen next. As a result, frontline leaders want reports that have key metrics and measures that fulfill the immediate needs on the floor. Instead of fancy reports that show how production relates to the relative position of Mars, they need to know how they are trending to plan and what they can do to meet it.

Now frontline leaders still care about the insights found in data. They still have access to computers and will want to perform a deeper analysis on their data. The key is making sure you have the right tool for the right job.

Now that you understand why I think mobile reporting is so important, let’s start building one out.

Create your own Mobile Reports

The first thing you will need to do is update your Power BI Desktop client to the February 2022 release. If you have not done so, you can download it here. This will allow you to use some of the new mobile reporting features that we will discuss in this article. After you have updated Power BI Desktop, open up an existing report hat you want to enable for mobile use.

Once you have your report finished, go to the view ribbon. From there, you can click on the Mobile Layout button to bring up the mobile view. You will notice that you will see a new blank canvas in the shape of a mobile phone.

Switching to the Mobile Reporting layout

While the canvas is blank, you will note that there is a pane on the right hand side full of visuals from your report page. We now can drag and drop visuals onto the mobile report canvas and adjust them accordingly.

Editing the Mobile Reporting canvas

As a result, you can optimize your existing report for mobile devices. Note that I did not add my table of top fifteen products sold. It is great info, but hard to read on your mobile device. We will make it available for consumers from their computers, but suppress it on a mobile device.

Take Mobile Reporting to the Next Level

You might be saying “Big deal! This has been around for a while! Why are you writing an article about this?!” But here is what I am loving about this update – we now have the ability to customize our visuals in the mobile format. When you select a visual on the mobile canvas, a new pane is available on the right side.

Think about this scenario – you have a bar chart but want to see what the actual values are. If you are on your computer, you can hover to discover what the actual amount is on the bar. But with mobile reporting, it is far more difficult to accomplish that. So to help consumers see what is happening, I can now add a data label without disturbing my original report.

To add data labels to by bar chart, I simply select it and expand the visualization pane. From there, turn on the data labels and customize accordingly. Note the new little icon that appears – the Power BI logo with a mobile device over it. This indicates that the visual is customized for the mobile experience.

Customizing the bar chart to use data labels

The best part of this whole thing is when I go back to the regular report view, the data labels will remain hidden!

Original report experience as seen on a computer

And just like that I have been able to optimize my mobile reporting experience while maintaining my original report! Amazing!

But Before You Change Your Mobile Reporting Experience…

Really make sure you know what you want to change! Once you break away from the original report configuration, you now have to update both the desktop and mobile report canvas. Even worse, there is no clear indicator that a report has been optimized for mobile users. As you consider your mobile reporting controls and lifecycle plans, you might want to consider options that ensure the mobile canvas is properly updated.

So have you built out any mobile reports? Do you have any great use cases for mobile reporting? If so, tell me in the comments below!

Stop Wasting Time by Using Power BI Themes

Power BI Reports serve several purposes, but they often consumed by larger audiences. As a result, we often strive to provide a quality viewing experience for our report consumers. Most people jump right in with the creation of their reports and neglect how the report is going to look and feel. This is common as we want to get to the good stuff as quickly as we can. But as they say, an ounce of prevention is worth a pound of cure. Taking some time to setup a theme file can really speed up the development process.

Why Are Themes Important?

So before you say that his is a lot of work to just make reports look pretty, it is really more than that. The look and feel of your reports are critical – especially in a self service environment. The look and feel of a report can be a subtle signal to the consumer of who created the report and the reliability of the data.

The Reliability of Poorly Branded Reports

I often work with customers who have created reports that are consumed by large audiences that use color palettes and fonts that do not align with the company branding. This is common with the first several reports that are created. Eventually, someone gets ahold of a branding guide and starts using elements in their reports. Updating the bars, colors, or lines to match the corporate branding helps add legitimacy to the report. Little by little, more corporate branding is added until the theme is fleshed out.

But what happened to those original reports? The ones that used no theme or some of the theme? They often remain neglected. New employees receive links to these reports and often ask themselves “Is this information accurate?” Eventually they will trust the data, but they spend weeks asking if it is correct.

It seems silly, but the branding can instill confidence in your reports.

Managing Branding Manually Hurts in the Long Run

Another common challenge I have identified is inconsistency between visualizations. Actually, it is difficult to even do within a single visualization. When you look at a simple column chart, you would think it is really easy to keep your fonts consistent. Here is a breakdown of where you can make changes to the fonts for a column chart:

  • On the X Axis
    • Value labels
    • X Axis titles
  • On the Y Axis
    • Value labels
    • Y Axis title
  • Legend
  • Small Multiple Grids
  • Column data labels (can be different for each series)
  • Visual title
  • Tooltip text

Add it up and you have a total of nine places to update the font so it is consistent! That is unbelievable! And if I have to do this for multiple visualizations, this can become a lot to manage. And without a consistent theme, I have to keep making these changes. So how do I prevent this from happening to me in the future?

Make Themes Work for You

Like I mentioned before, a little planning can go a long way. Here are my steps to help simplify the process.

Step 1 – Find the brand identity documentation for Your organization

Hands down this is sometimes the hardest part of the process. If you work at a company that has a strong brand identity, this should not be difficult. There is likely a brand identity guide or a PowerPoint template that has all of the details you need such as the color HEX or RGB codes and fonts located in the margins of the slides.

If you cannot find a brand identity guide or PowerPoint that has that information, I always recommend becoming friends with someone in your marketing department. They have this information readily available for their projects. It might cost you a few drinks at Happy Hour on Friday night, but it is a lot easier than having to get this information manually.

If you are not as lucky, you can still get this information. It might be a little more difficult, but you can find it embedded inside other resources. You can usually find a PowerPoint template that has the basic colors and fonts identified. If you select a text box in a presentation, you will likely find the font used for the majority of the presentation. You can also click on the font colors to see if they are the ones your organization uses. If they are there, select a color one at a time and then click on more colors. Doing so will bring up the RGB and HEX color codes for the selected color. I like to copy the HEX colors as they are easier and paste them into an Excel spreadsheet with the color name so I can quickly reference them.

Still cannot find your colors? Not to worry. Find a digital copy of your company’s logo and add it to a blank PowerPoint presentation. From there, use the eyedropper function under the font color dropdown and use it to extract the key colors to your organization’s logo. Then follow the steps above to extract the color codes.

Step 2 – Build out your Power BI Theme

With the RGB or HEX color codes in hand, it is time to update your Power BI theme. Go to the View tab in Power BI desktop and click on the drop down to the right of the themes.

Accessing the Power BI Theme customizer

From here, you can start to build out your theme. Start by adding a name for your theme. From there, update the color palette. Simply add the RGB or HEX codes you identified into each of the positions. Keep in mind that the order of your colors will match the order they are selected for legends. If you have a particular combination of 2-3 colors you like to use a lot, make sure they sit in the first three positions so they can easily be leveraged.

Setting up the color palette for your Power BI Theme

Next, you will want to address the text in the report. By default, Power BI is set to use a blend of Segoe UI and DIN fonts. The list of fonts is limited to ensure they stay web safe and visible in the Power BI service. Your first choice might not be in the list, but most organizations have a secondary font that is for such an application. You can also set default sizes and colors for generic text, titles, cards/KPIs, and tab headers.

Customizing the text for your Power BI Theme

Step 3 – Tweak your Power BI Theme until it is set

Once you have the basics in place, you can start making other adjustments to your theme. Other items you might choose to customize include visual backgrounds, borders, report backgrounds, and other adjustments. You have a lot of flexibility to make adjustments that meet the needs of your reports, so play around with them. Whatever you do, try as hard as you can to avoid the formatting pane for your visuals.

Understand that this process can take a little time, so do not be afraid to spend more time than expected. The more customizations are set in the theme, the less customizations you will have to apply to visualizations when you build your reports!

Step 4 – Export your Power BI Theme

When you are comfortable with the theme you have developed, it is time to export your theme so it can be used with other reports.

In Power BI Desktop, go back to the View tab and click on the dropdown next to themes. Select Save Current Theme and save it to your OneDrive. This way it will be automatically backed up in the event of a computer emergency.

Step 5 – Deploy your new Power BI Theme

This is where things can get a little hairy. You have a few options for deploying your theme to others within your organization.

1). You can distribute the Power BI Theme file you created. Users can import the theme every time they want to use it when building a report.

2). You can create a Power BI Report using the theme. Leave it blank and save it as a Power BI Template (*.PBIT) file and distribute it.

Both solutions work, but they feel a little clunky. For me, I have found most of the report creators are quite savvy with theme files, so it is not a big deal. However, if you are interested in building a strong citizen developer community, this could prove to be challenging.

Best practices/considerations for building Power BI Themes.

When you build out your theme, keep these best practices and considerations in mind to help drive the best experience for your report consumers.

Check your theme against multiple visualizations

The temptation is always there – if you only use bar and column charts you only check those visuals. When building out your theme, make sure you try all of the different types of visualizations to make sure your theme works across all available visualizations.

Consider a style guide for reports

If you followed the first best practice, this should be an easy one. Take the report file you used to test the theme and create a sample report. From here, add in key information such as how to label data, types of charts, and other organizational standards that have been set forth by your team. This will help content creators have a roadmap for building quality reports.

Consider color contrast to make reports accessible to all

This might be a bigger concern if you do not have an existing style guide from marketing, but is always an important consideration. Content is difficult to consume if creators use poor color selections. Even worse, it causes strain for users with visual disabilities who have trouble viewing the content. Use a tool like WebAIM’s Contrast Checker to ensure the best experience for all report consumers.

Conclusion

It is a lot of front end work, but I promise you that you will love how easy it is to deploy reports when you have a custom theme. Take the time to build it out right and you will never have to mess with updating individual visuals ever again.

Are you using themes already? Do you have some tips/tricks on how to use them? How are you distributing themes? Leave a comment below and let me know!

Powered by WordPress & Theme by Anders Norén