Power BI – Using Change tables to compare data
Have you ever wanted to compare changes in your data without having to create duplicate copies, as well as make it really simple and easy for users to do some self-service comparisons?
In this example I am going to show how I created and then used Change tables to compare data where you can select multiple items to compare.
This is best shown with an image of my completed Power BI Desktop file.
The data below is from Gap Minder in which it shows how many hours people work by country over time.
What I wanted to do was to compare based on Australia which countries work more and which countries work less. As you can see from above both New Zealand and Poland work more hours than Australia whilst Norway works significantly less.
I have also created other Power BI solutions where I have used it based on Actuals from Sales, compared to different budget versions to see how accurate the budgeting process has been.
Creating the Change Table
In the steps below I will show you how to create the change table.
I called my Original
table ‘Working Hours’, and this is what it looked like
What I did was I went to the Modeling ribbon and then clicked on New Table
Below is the syntax which makes an exact copy of my ‘Working Hours’ table but with a new name
Working Hours Comparison = ‘Working Hours’
- And now I had a copy of my Change table
- Next is where I create the Comparison measures.
Creating the Comparison measures
In the steps below I will show how I created the comparison measures.
NOTE: I had already created the measures in the ‘Working Hours’ table already, so re-creating them in my ‘Working Hours Comparison’ table is so that I can then compare the values. So they are almost identical in that the measures just relate to either the ‘Working Hours’ or ‘Working Hours Comparison’ table.
Measure – Avg. Working Hours (Comparison)
Avg. Working Hours (Comparison) = DIVIDE([Hours Worked (Comparison)],[Total Rows (Comparison)])
- This measure is getting the Average Working hours for our Comparison Table ‘Working Hours Comparison’
Measure – Difference
12345678Difference =IF (// The [Avg. Working Hours] from "Working Hours" or [Avg. Working Hours (Comparison)] from "Working Hours Comparison" is Blank// Then make it Blank, else return the Calcualted MeasureISBLANK ( [Avg. Working Hours] )|| ISBLANK ([Avg. Working Hours (Comparison)] ),BLANK (),([Avg. Working Hours] - [Avg. Working Hours (Comparison)] ))
- What it first does as explained is it checks to see if either measure is blank and if it is blank it then makes it blank else return the value.
- This measure is then calculating the difference between the two tables.
Measure – Difference %
12345Difference % =IF (ISBLANK ( [Difference] ),BLANK (),1 - DIVIDE ([Avg. Working Hours (Comparison)], [Avg. Working Hours] ))
- This final measure is calculating the percentage difference between what has been selected.
So now my ‘Working Hours Comparison’ table looked like the following below.
Creating the Slicers for the Comparison
The next thing that I needed to do was to create the comparison slicers so that I could then compare between the different countries.
To do this I needed to create 2 slicers. This was done by creating a slicer from each of the ‘Working Hours’ and ‘Working Hours Comparison’ tables
NOTE: This is very important so that the change
tables will reflect the correct data.
For the Country that I wanted to compare everything to I need to use the Country
column from my ‘Working Hours Comparison’ table
- This is so that I could get the comparison’s to work as expected.
I also configured this slicer that it could only select one value by making sure that Single Select was On
For the Countries to compare I used the Country from the ‘Working Hours’ table.
For this slicer I make sure that the Single Select was Off, this is so that I could select
countries to compare.
- Once I created the above it then enabled me to be able to select a Review Country and then one or many Countries to compare to.
Creating the Report
Then the final part was for me to create the report so that I could then see how they compare.
NOTE: I did create some other calculations in order to make it easier to understand and see what has been selected.
As you can see with my original image that when comparing
Australia with New Zealand, Norway and Poland, Norway works a lot less hours than Australia.
Now I could also select Norway as the Review Country and then compare
Norway with Korea Republic and Netherlands, I could then see below that the Netherlands works less than Norway (I think I might want to move there @Kjonge seems to enjoy it!), whilst
Republic has by far the highest
So I have been able to show how you can use change tables to easily compare your data. As with my example I was able to compare which countries have higher or lower working hours in a quick, easy and efficient manner. As well as the ability to select multiple countries, so that it was easy to see how each country compared.
I found this to be really useful when comparing Sales vs Budget amounts because it makes it easy to see where things are going well and where in other parts of the business it requires some urgent attention.
You can test it how below