- Replace blank data labels with zeros — to improve data visibility
- Draw a dynamic benchmark line — to compare actuals vs. target
- Add a chart to the tooltip — for better insights into details
- Stack visuals evenly — for a uniform layout
- Overlap visuals — to bring more data together
1. Replace blank data labels with zeros — to improve data visibility
Although it may seem straightforward, it can actually become tricky sometimes. Let’s consider an example where you have two related tables.
Add a bar chart to display the “total hours” for each asset.
Asset Numbers without hours are not shown on the chart yet. To bring them up select “Show items with no data”...
Although it displays missing Assets Numbers now, it doesn’t show anything on data labels as there are no entries for them in the “Asset Hours” table. Let’s say we want to see “zeros” instead of nothing for those Assets.
I will create a measure for Total Hours which uses ISBLANK() function to display “zeros”
Total Hours =
VAR varScanHours = SUM ( 'Asset Hours'[Hours] )
RETURN
IF ( ISBLANK ( varScanHours ), 0, varScanHours )
But it doesn’t work, because DAX is scoped to the “Asset Hours” table and since there are no entries for those assets in that table, it doesn’t produce the expected outcome. To get around this problem, we can use a simple (dirty) trick in the DAX measure, we will add a non-significant decimal zero number to the sum to get around this.
Total Hours = SUM ( 'Asset Hours'[Hours] ) + 0.0001
2. Draw a dynamic benchmark line — to compare actuals vs target
If we have to cover a business use case that requires comparing Target vs Actuals, then we can add a target line that runs across the above bar chart. This target line (or benchmark) serves as a reference point and allows for easy visual comparison between the actual hours and the target hours for each asset.
To do this, select the bar chart and navigate to the “Analyses” menu and then click on “Constant line”. If you go by its name, you may think it doesn’t support dynamic values (adjustable based on the user input).
But when you click on “+ Add line” you can see that there are options to enter either a constant value or set an expression(fx) that can change dynamically.
So, let's create a DAX measure to calculate target hours assuming 12hrs of operations per day. Click on the fx icon next to the line value and select this newly created “Target Hours” measure.
Target Hours = DISTINCTCOUNT('Asset Hours'[Scan Date]) * 12
Next, turn on the Data label, change the Horizontal position to Right, set Style to both, and rename the line as “Target Hours”. Voila! now you can see a line that runs across the chat and acts as a benchmark to compare with.
3. Add a chart to the tooltip — for better insights into details
In Power BI, there are different techniques to show a lower level of detail. Tooltips, Drill-downs, and Drill-throughs are the most commonly used approaches. Tooltips can also be used to show detailed/sub-charts.
Create a new tab titled “Daily Trend — Tooltip” and then go to Canvas settings to set the Type as Tooltip.
Add an area chart to this new page to show a daily trend of total hours.
Go to the first page, select General settings of the bar chart, and expand the Tooltips > Options. For Type select Report page and for Page select the newly created page.
Now hover on the bar chart to show daily trends for each asset. The tooltip is smart enough to filter the area chart based on the selected bar(Asset).
However, we have a minor issue here, this tooltip page has some extra space at the top. How can we fix that? Unfortunately, size settings are greyed out. Therefore, let’s change the canvas Type to Custom and then adjust the page height. Your tooltip settings on the first page will still work.
The final view.
4. Stack visuals evenly — for a uniform layout
Sometimes it gets messy when you have to add multiple visuals to the dashboard. Group formatting options can help us out in such situations. Let’s add a few KPIs to the dashboard.
As you can see, they are unevenly placed and non-uniformly sized.
Let’s do a couple of things to fix this. First, select all the KPI cards, navigate to General Properties, and set a fixed Height and Width.
Second, move the first and last cards to opposite corners creating some gap so the rest of the cards can be aligned between them. Now, navigate to the Format menu to select Align middle, and Distribute horizontally to evenly layout KPI cards.
5. Overlap visuals — to bring more data together
Power BI provides the flexibility to freely move visuals across the dashboard and even overlap them. This feature will come handy when you want to extend a visual to show some additional information.
Continuing with our Asset dashboard, first, create a drought chart to display the distribution of hours across different Asset Classes.
Second, create a card to show the total hours and then remove its background color.
Now drag this card to the center of the drought chart. Navigate to the Format menu and select Bring forward to place the card in the foreground. Group both visuals so it's easy to resize or move them together.
We have a slight problem here, when you make a few selections on the donut chart (after publishing it to the cloud), you will notice that the card has disappeared. This happens because Power BI thinks that you want to bring the donut chart to the foreground.
To fix this, remove the background color for the donut chart, add a rectangle shape on top of it, and set the shape’s background to white.
This time we will do the stacking (Layer order) and grouping using the Selection panel under the Format menu. In the Selection panel, move Shape (rectangle) into the group we created earlier and place it at the end (bottom of the stack). This arrangement will make sure that card is always visible.
Final view of the dashboard.
For more hacks please read…
Useful, they may be. Sample files, download from here, you can! -Yoda.
