Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
In the steps below I will show you how to create the change table.
Working Hours Comparison = 'Working Hours'
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.
Avg. Working Hours (Comparison) = DIVIDE([Hours Worked (Comparison)],[Total Rows (Comparison)])
Difference = 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 Measure ISBLANK ( [Avg. Working Hours] ) || ISBLANK ([Avg. Working Hours (Comparison)] ), BLANK (), ([Avg. Working Hours] - [Avg. Working Hours (Comparison)] ))
Difference % = IF ( ISBLANK ( [Difference] ), BLANK (), 1 - DIVIDE ([Avg. Working Hours (Comparison)], [Avg. Working Hours] ))
So now my 'Working Hours Comparison' table looked like the following below.
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.
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 Korea Republic has by far the highest working hours.
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 with this link:
Power BI Change Tables Example
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.