Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

GilbertQ

Using Change Tables to Compare Data

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

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

Hi @Star_Dust

 

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

hi ,

I have one small doubt 

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

explain some clearly

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?

Hi, @GilbertQ, 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 

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.

HI @GilbertQ

 

Congratulation for your work. 

 

Can you send the pbix because I am looking to do something similar. 

 

Thanks

 

Pierre

 

Hi @pierre59123

 

Please can you send me a private message with your email address and I can email it to you.

 

Regards

Gilbert

I'm looking similar to this one. Could you please send me the pbix file to this mail ID owenitian@gmail.com if you dont mind?

Hello, 

 

Would it be possible to overlay a metric slicer over this visual? So not just having hours worked, but other data that can be compared?

 

Many thanks!

 

 

Hi @jones787

 

This could be done by using a disconnected table and measure, in which you could use a slicer to change the measures.

 

Here is a blog post I did previously explaining this: https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/

 I have the similar requirment. Can you send the pbix file to email mehra_deepak@hotmail.com ?

 

 

Thank you for sharing your great work. 

I would like to use your techinique to tackle my problem at hand.

I am new to Power BI. Can you please send me the pbis file to thousandcosmos@gmail?

 

Hi @c_utaka there is a link to the file at the end of the blog post 

Hi,

 

This is exactly what I am looking for but the link gets me to just the visual without the comparison measures( the part that I cannot quite get right.  Can this be posted or emalied to bcobrien77@yahoo.com?

Thanks