Using Change Tables to Compare Data

by guavaq Super Contributor on ‎10-12-2017 09:32 AM

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
    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)] ))
    • 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 %
    Difference % =
    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 multiple 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 Korea Republic has by far the highest working hours.

Conclusion

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

 

 

Comments
by Star_Dust Frequent Visitor
on ‎10-24-2017 03:43 PM

Can you advise what's this slicer's name please? thanks

by guavaq Super Contributor
on ‎10-24-2017 06:58 PM

Hi @Star_Dust

 

When you download and open the PBIX file and click on the Slicer it will then give you the slicers name?

by damaganesh Occasional Visitor
on ‎10-25-2017 08:31 AM

hi ,

I have one small doubt 

how to add the calender date (report) ,please give me the steps 

explain some clearly

by guavaq Super Contributor
on ‎10-25-2017 06:24 PM

Hi @damaganesh

 

When you say "To add the calendar date" what do you mean?

 

The relative Date slicer, or the date fiels as a slicer?

by Mikkelnaes Visitor
on ‎11-10-2017 05:47 AM

Hi, @guavaq, thanx!

 

The slicer in PBI is by far not that great than the one you have here. Can you explain, is it a custom slicer? That is, it got the same features but the one you choose countries with is better in visualisation. 

 

Thank you again, this is great

 

Mikkel 

by guavaq Super Contributor
on ‎11-12-2017 02:43 PM

Hi @Mikkelnaes

 

That slicer that I used is from the Visuals store and it is the Chiclet Slicer.

 

If you download and open the PBIX file you will see it in there as well as the properties.