All Posts By

James Honner

Power BI Dynamic Measures & Dimensions

By | Data Visualisation | No Comments

Power BI Dynamic Measures & Dimensions – Part 1

A great way to take your Power BI Reports to the next level is by making them fully interactive by incorporating PowerBI dynamic measures & dimensions. With this clever use of DAX, it’s possible to slice your data the way you want without leaving the dashboard screen.

In this 2-part series we’ll look at creating dynamic measures & dimensions within your PowerBI reports in part 1 and then continuing that into visual titles in part 2.

Using this concept greatly enhances the end user experience as they can monitor/analyse multiple measures and dimensions in a single report page at the click of a button making this a highly desirable solution.

In the PowerBI report example below, we have a financial dataset which includes revenue, expenses & taxes and from these we can calculate profit. We have created 1 set of measures for profit, revenue, expenses & taxes and 1 dimension for the time periods Fiscal Year to Date (FYTD), Quarter to Date (QTD) and Month to Date (MTD).

This will allow end users to view, as seen in the examples below, Profit by FYTD or QTD or MTD as well as the variances to the previous FYTD or QTD or MTD and all the visuals and visual titles will update when a different measure or time period is selected.

Profit FYTD

Profit QTD

Profit MTD

How to

There are quite a few steps involved to get this concept up & running but the benefits to your report consumers will be well worth the effort.

  1. Create the base measure for Profit, then create the Time Intelligence measures for the current periods, Profit FYTD, Profit QTD & Profit MTD and then create the measures for the previous periods, Profit PFYTD, Profit PQ QTD, Profit PM MTD and repeat this process for Revenue, Expenses & Taxes.

  1. Create 2 new tables, one for measure selection, this allows end users to select from Profit, Revenue, Expenses & Taxes measures, and one for time selection, this allows end users to select from FTYD, QTD & MTD time periods.

Dynamic Measures table

 PowerBI Dynamic Measures Table

Dynamic Dimensions table

  1. Create the base measure for the Measure Selection and Time Selection These 2 measures utilise the SELECTEDVALUE DAX function, becoming the foundation for the dynamic measures we create for the visuals within the report page.

Below is the Time Selected measure.

 

  1. Next we create the Selection 1 and Selection 2 These 2 measures utilise the SWITCH DAX function. Selection 1 is for the current period measures FYTD, QTD & MTD and Selection 2 is for the previous period measures PY FYTD, PQ QTD and PM MTD. Below is the Selection 1 measure.

PowerBI Dynamic Measures DAX

 

  1. Finally, we create the variance measures between Selection 1 & Selection 2 allowing us the show the variances by $ value and % value between the selected time periods i.e. variance between FYTD and PY FYTD as seen below.

 

Bringing it all together

Using your preferred slicer, in this example the Chiclet Slicer (available via the PowerBI Marketplace), we create 2 slicers, one for the Selected Measure and one for Selected Time. These slicers allow your report consumers to dynamically change measures & time periods within the report page.

Below is the Selected Measure Chiclet Slicer.

PowerBI Dynamic Measures Slicers

With all the setup now complete, we create the visuals for the report page using either or both the Selection 1 and Selection 2 measures and you’re done!

Summary

If you’d like to take your PowerBI Reports and Dashboards to the next level and need help, please contact us to discuss how we can assist your organisation.

PowerBI Tooltip

PowerBI Tooltips

By | Data Visualisation | No Comments

PowerBI Tooltips enhance your reports

A great way to take your Power BI Reports to the next level is by using PowerBI Tooltips. Report Page ToolTips (RPT). RPT’s allow you to enhance your reports by giving your end users more information without taking up any real estate, cluttering the report canvas.

In the example below, we have a donut chart showing Revenue FYTD by Region. One great way to enhance visual this is to add a RPT showing the Top 5 Stores for each Region as your end users hover over the donut chart.

PowerBI Tooltips

Target visual for PowerBI Tooltips

 

In the example below we’ve hovered over the ‘North America’ Region and the RPT shows us the Top 5 Stores by Revenue FYTD for that Region.

PowerBI Tooltips

Tooltips in Action

 

When we hover over the different Regions the RPT changes to show the Top 5 Stores for that Region.

PowerBI Tooltips

PowerBI Tooltips Changing context

 

As well as the RPT, we have setup a drill through on this donut chart and can still perform this action by right clicking and selecting the drillthrough option as shown below.

PowerBI Tooltips

Drilling through for more detail

 

In our second example, we demonstrate that RPTs can also be used on bar charts. This bar chart shows Revenue FYTD by Country and when you hover over a Country the RPT shows the Revenue FYTD by month for that Country. These also change as you hover over the different Countries.

PowerBI Tooltips

PowerBI Tooltips on a different visual

 

NB: This report also has dynamic measures and visual titles which we will cover in upcoming blogs.

How to create PowerBI Tooltips

In only a few steps you can create PowerBI Tooltips (RPT’s) in your reports, so let’s go through those steps now.

  1. Add a page in your report and in the Visualizations tab, set the Page Information tooltip slider to On and give it a name. In this example we’ve named it TT – Country FYTD
PowerBI Tooltips

Enabling PowerBI Tooltips

 

  1. Create a visual or visuals, below we have a line chart and 2 card visuals for our RPT, within the report canvas as normal and size the page appropriately. In the Visualizations tab you can set the page size to the default Tooltip or customise the size to get the best fit for your chosen RPT visual.
PowerBI Tooltips

Defining PowerBI Tooltips size

 

  1. Once you’ve finished creating the tooltip visual, hide the page by right clicking on the page name.
PowerBI Tooltips

Hiding the PowerBI Tooltips page

 

  1. On the report page, select the visual the RPT will appear on and go to the Tooltip settings in the Visualizations tab, set the Tooltip slider to On, the Type to Report page and select the tooltip visual you created, in our example it’s TT – Country FYTD
PowerBI Tooltips

Linking the PowerBI Tooltips to a visual

 

And you’re done! It’s as simple as that.

In upcoming blogs we’ll go through some more advanced concepts, dynamic measures/attributes as well as dynamic visual titles so stay tuned for that.

If you’d like to take your Power BI Reports and Dashboards to the next level and need help, please contact us to discuss how we can assist your organisation.