Category: Report Design

Conditional Formatting with a Column Chart?!

On April 14, 2022

In DAX, Report Design, Visualization Standards

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!

What If Scenarios in a Power BI Data Model

By Dominick Raimato

On April 7, 2022

In DAX, Report Design, Simulation

What If? A common question we ask all the time. What if we increased our revenue 20% from last year? What if we increased our margins by 2%? We ask these questions frequently, but do we do anything to solidify next steps to figure these things out?

Power BI makes it easy for us to run these scenarios in a dynamic format with What If Parameters. However, I find most people avoid using them because they need to do some DAX to integrate them. Today, I want to demystify this and help you build out your own scenarios. Our scenario will allow us to create a revenue goal that we can adjust based upon the previous year.

Setting Up Our Revenue Measures

To get started, it is important to build a measure to calculate both our current year and previous year revenue. This will provide a base to perform our comparisons.

I start with a base calculation that will summarize our sales data for the current year:

CY Revenue = 
SUM(SalesAggregated[Revenue])

Which results in this column chart:

Next, we will leverage our current year revenue to calculate the past year’s revenue:

PY Revenue = 
CALCULATE([CY Revenue],   SAMEPERIODLASTYEAR(SalesAggregated[Month]))

This results in the clustered column chart we created in my article last week:

The result of our measures allows us to see the previous year’s revenue for all of 2021, but only the current year revenue for Q1 of 2022. Our goal is to next add a What If Parameter so we can create our 2022 goals.

How Does a What If Parameter Work?

What If Parameters are built out in the Power BI Report. In fact, when you create a parameter, it does a lot of the hard work for you. Let’s start by creating a parameter and see what happens in the background.

Creating a What If Parameter

To get started, go to the modeling tab and select new parameter. A new window will appear which will require some input. First thing we will want to do is set our parameter name. I am calling mine “Revenue Increase %”.

Next, we need to configure the parameter. Because we are working with percentages, we need to change the data type to decimal number. Then, we need to set our minimum and maximum values. Because we do not want to lose revenue, our minimum will be zero. Our maximum will be 2 which will allow a 200% increase of revenue.

Lastly, we need to set the incremental value. We are going to specify .05 which will allow 5% increments. We will also set our default our value at zero. You will also notice that there is a box checked to add a slicer to the report canvas. We will leave that checked and click ok.

Setting Up a What If Parameter in Power BI

The most obvious item created is the slicer that appears on the canvas. But there is so much more to this simple process that happens in the background.

What Else Is Created With a What If Parameter?

To answer that question, let’s head over to the table view of the report. A new calculated table shows up on the left hand side that matches the name of our parameter. When we select it, you can see there is only one column which lists our percentages from 0 to 200 in decimal format.

But how do we leverage these numbers? The slicer will limit which value is being used, but how do I build a relationship to my revenue? While there is only one column on the table, there is a measure that was created as well:

Revenue Increase % Value = 
SELECTEDVALUE('Revenue Increase %'[Revenue Increase %], 0)

The SELECTEDVALUE() function looks for the value you have selected with the slicer and returns a single value. In this scenario, it is looking for a single percentage value in that column that was created. If it finds more than one value, it returns a zero instead.

Is There Any Other Setup Required Before We Use It?

At this point, everything is in place for a usable What If Parameter. However, I am always a fan of cosmetics and making things look a little cleaner. Everything is currently in a decimal format, but we are looking at everything in percentages. As a result, we will want to select both the column and the measure created and make sure they are formatted as a percentage with no values after the decimal place.

Formatting What If Parameters for Percentages

We can also consider adding a card to help highlight the percentage we want to use over last years numbers so it is easy to find. We can use that measure created to make it easier to see on the report canvas. This is helpful if you are syncing the slicer across multiple report pages.

Our What If Parameter on the Report Canvas

It is important to understand what is being built in the background because it lays the groundwork for using the What If Parameter in our report.

Applying a What If Parameter

Now that our parameter is set, we need to apply it to our report. The first step is creating a new measure that will calculate our goal.

CY Goal = 
[PY Revenue] * ([Revenue Increase % Value] + 1)

If you remember, our goal was to calculate our current year goal as a percentage over our previous year revenue. To accomplish this, we used our previous year measure and multiplied it against the measure we created from our What If Parameter and added one. Why add one? Because we want to grow our revenue, so we add one to set the base as 100% and can grow it from there.

With our new measure created, we can apply it to our column chart:

Previous Year Revenue, Current Year Revenue, and Current Year Goal in a Clustered Column Chart

Now that everything is in place, you can adjust the goal at the beginning of the year to set the goal. At the end of the year, you can easily adjust the goal to align with what happened over the entire year to determine what your goal is for next year.

Next Steps

We started with a single What If Parameter, but if you have multiple you want to adjust, you can create more of them. In the event that you have a complicated list of values that has a lot of rules, you can consider some other options as well. While we did create everything with a few clicks, you have the ability to write all of the DAX on your own. You can build out a table, create the slicer, and a measure to leverage it. The reality is you can create flexible parameters to meet any scenario!

How about you? Do you have any reports that could benefit from a little flexibility? Have you used What If Parameters before? If so, tell me in the comments below!

Conditional Formatting – Moving to the Next Level

By Dominick Raimato

On March 24, 2022

In Report Design, Visualization Standards

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

By Dominick Raimato

On March 17, 2022

In Report Design, Visualization Standards

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.

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.

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.

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!

By Dominick Raimato

On February 24, 2022

In Report Design, Visualization Standards

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.

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

By Dominick Raimato

On January 10, 2022

In Look and Feel, Report Design, Visualization Standards

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.

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!