PowerBI.tips

Tag: Conditional Formatting

Dynamic Visuals using Buttons
Sometimes, we want the users to see different metrics, but do not want to take up too much space on our page. The scenario we are going to walk through is how to build just one visual (in this case a bar graph). It will include a toggle that allows the user to select their desired calculation, either the sum of Volume, Dollars or Margin.

Final Solution

With buttons, we can change specific visuals on a page. Recently, with the release of conditional formatting on titles and backgrounds, we have some new methods to make this easier for the report author and cleaner for the report consumer.

The Build

Before we start, turn on the selection pane and bookmark pane. They can be turned on by clicking on the View ribbon and checking the correct boxes.

First, we’re going to create our control table. This will be a disassociated table. This table should not have any relationships to any of the other tables in our model. We just need to enter a numeric ID and a description of what we want. Click on the Enter Data button found on the Home ribbon. Enter the following data as shown. Click the OK button to close the Create Table dialog box.

Now that’s set up, we can write our measure. This measure will see what is selected in the Number_ID column of our control table, then return the appropriate calculation. Use a switch statement to select the correct calculation. Create the following measure:
```
Selected Calculation = 
SWITCH(
  SELECTEDVALUE(Control[Number_ID])
   ,1,SUM(Sales[Volume])
   ,2,SUM(Sales[Dollars])
   ,3,Sum(Sales[Margin])
   ,SUM(Sales[Volume])
)
```
Note: See there is a default value listed in the switch statement. The default calculation means that if nothing is selected, SUM( Sales[Volume] ) will be returned. The default value is represented by the last property in the switch statement.

Time to set up our visual. Add a bar graph with Category on the axis and the new measure, Selected Calculation, in the values fields. Then add a slicer for the Number_ID column. The Number_ID column comes from the control table we added earlier.

Switching the slicer can now change the graph to show the different calculations.

The next stage is to add three buttons to the top of the graph. In the Home tab of the ribbon, click Buttons and select Blank. Make sure the outline colors and outline width match on all objects, Buttons and chart outline.

Tip: Make sure you label your buttons in the Selection Pane. The selection pane can be turned on by clicking on the View ribbon and checking the box labeled Selection Pane. To Change the name of the button, double click the name listed in the Selection Pane. Giving a title (such as Button_Volume) will make it easily to see what visual items are on the page.

After this, it’s time to add the bookmarks.

The bookmark pane can be turned on by clicking on the View ribbon and checking the box labeled Bookmark Pane.

Step 1:
- Select a value of 1 in the Number_ID slicer.
- Select the slicer (and only the slicer) in the Selection pane.
- Click “Add Bookmark” in the Bookmarks pane.
Step 2:
- In the Bookmarks pane, right click the bookmark and rename it to Select 1.
- Right click again, and untick “Display” and “Current Page”. Select “Selected Visuals”.
Now repeat step 1 and step 2, but do so with the values of 2 and 3 from Number_ID slicer. Name these bookmarks Select 2 and Select 3. You should finish with three bookmarks, each that filters Number_ID to a different value. You can test the bookmarks by clicking on them once in the bookmark pane.

On Button_Volume, assign the Select 1 bookmark (as Number_ID 1 refers to volume). To do this, click on Button_Volume in the selection pane. In the visualizations pane for this button, go to the property named “Action”. Turn it on, change the type to bookmark, and choose Select 1 in the dropdown.

Repeat for Button_Dollars and assign Select 2. Then for Button_Margin and assign Select 3. Now the buttons can change the graph, but it’s a bit hard to see what is selected.

Add Conditional Formatting

This is where conditional formatting can help us! Select Button_Volume in the selection pane. Then in the visualizations pane, turn on the background property, select the ellipsis and click conditional formatting

Here’s the settings we want:

This is going to apply a rule if the Number_ID selected is 1, to give the button a blue background. As there are no other rules, any other number selected will default to the white.

Now, apply the same steps to the other two buttons, but make the rule “If value is 2” for Dollars, and “If value is 3” for Margin.

To tidy up, hide the slicer and turn the visual headers of all buttons off. You can click on the eye next to the slicer in the selection pane to hide it.

Turn the visual headers off by clicking the button, then in the visualizations pane.

Great! Now the tab shows the selected button and correct measure:

To make it even clearer, apply conditional formatting to the title of the graph. On the graph, open conditional formatting. Set it to field value and use the type field in the control panel.

Using this control table allows for greater flexibility. We can add more calculations, easily edit them or even sync across pages, all without having to re-record any bookmarks.

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
August 8, 2019
Fixing the Truncating Bar Chart

The more you work with Power BI Desktop it is more than likely you will find some tool limitations that impact your overall design pursuits. As I have worked with data visualization software, I find there is always a balance between what I want to make and what is possible. The more you become familiar with your visualization tool, the better you think of report designs that are both beautiful and feasible. One such design style that I use is to limit the use of slicers on the report page as much as possible. My solution for removing slicers is adding a stacked bar chart or a stacked column chart. The chart can be used as a slicer because you can click on the data bars and filter the page of data. One of the visualization limitations I’ve had to work around was the ability to make a stacked bar chart with long y-axis titles. In order to overcome this limitation we need to fire up our creativity to figure out another way to more accurately control the y-axis labels and the associated bar chart.

There are two main issues we will need to solve:

First issue, when you have text along the y-axis in the stacked bar chart, the text becomes truncated. See below.

Stacked Bar Chart

Note: All the text next to each bar is truncated if the text is to long. This can be fixed by extending the visual to a ridiculous length, as illustrated by the following picture. While this solves the text issue, this totally defeats the purpose of this visual, provide a “slicer” that can be used to filter the report page with minimal space consumption.

Super Long Stacked Bar Chart

Second issue, when there are super small values next to large values, it is almost nearly impossible to click on the bar to enable the filtering. In the example image above, it’s easy to click on the value of 1,300 but almost impossible to click on the value of 10. Womp, Womp, and clicking on the bar text value does not enable filtering, insert ridiculous horn sound, or other familiar but annoying horn sound (as recommended by one of our readers Terence).

After some playing around with various visuals here is what I came up with. First, you must change the visual from a stacked bar chart to the matrix visual. On the Visualizations pane click on the Matrix visual.

Matrix Visual

This will change the visual to a matrix. It’s a little busy so we will clean it up a bit. On the Visualizations pane change the Matrix style to None, then open up the Subtotals section and set Row subtotals to Off. Your visual should now look similar to the following:

Change Matrix Style

Next we will add the “bars” to the visual. Open the Conditional formatting section and turn Data bars to On.

Turn on Data Bars

Short and sweet. Now we can properly resize the “text labels” of the y-axis and when we try to select small values such as 10, we are presented with a little grey selector bar, enabling us to select very small values.

Grey Selector Highlighting Bar

When you compare all three items side by side you can see that the most condensed version is the Matrix visual with conditional formatting bars. This provides you much more control when dealing with data that contains long text labels.

Comparison of Bar Charts

Note: There are many ways you can format your matrix to get the desired look. This tutorial is simply covering one type of look. Additionally, you could hide the text and grid completely by making the grid and column title colors of those match the color of your background, or use could choose one of the many of the grid type options to fit your style needs.

Thanks for following along, as always if you found this helpful please share it with someone who might find this helpful.

November 14, 2017