Data set comparison... what's the best method?

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi Team,

Looking to understand how others would tackle the following task...

Here's a very simple example of my data set:

VersionMonthForecast
AJan10
AFeb3
AMar8
AApr5
AMay3
AJun7
AJul8
AAug1
ASep10
AOct9
ANov3
ADec4
BJan4
BFeb5
BMar8
BApr2
BMay4
BJun10
BJul4
BAug6
BSep6
BOct2
BNov10
BDec5
CJan2
CFeb8
CMar1
CApr8
CMay5
CJun7
CJul2
CAug5
CSep4
COct1
CNov2
CDec2

<tbody>
</tbody>

On occasions, a user will want to compare two different versions - e.g. 'A' and 'C' - to see where movement has occurred.

The challenge:


  • Establish a way of showing the versions that are available so that the user can choose which two to compare;
  • Create a table containing both of the versions chosen as well as the delta (difference) between the two;
  • Pass the resulting data set back for analysis.

I'm sure DAX measures could be used for this, but I'm wondering if there is a way of doing it in Power Query, perhaps more efficiently?

Thanks in advance to any potential helpers!

Cheers,

Matty
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I would use power pivot, not power query.
Load all the data into a table called data
Load a version table (unique version codes) twice. Call one version and the other ComparisonVersion
connect both version tables to the data table, version first, then comparisonversion. The latter relationship will be Inactive.
write measures.
Total forecast = sum(data[forecast]]
total comparison forecast = calculate([total forecast],userelationship(data[version],comparisonversion[version]))
difference = [total forecast] - [total comparison forecast]
 
Upvote 0
I would use power pivot, not power query.
Load all the data into a table called data
Load a version table (unique version codes) twice. Call one version and the other ComparisonVersion
connect both version tables to the data table, version first, then comparisonversion. The latter relationship will be Inactive.
write measures.
Total forecast = sum(data[forecast]]
total comparison forecast = calculate([total forecast],userelationship(data[version],comparisonversion[version]))
difference = [total forecast] - [total comparison forecast]

I'm trying to learn from this useful tip but can't make it work. I guess the first problem I have is trying to load the same table twice , any suggestions?

Peter
 
Upvote 0
Hi Matt,

Thanks for replying.

I kind of understand the logic, but I'm struggling to implement something that does what I need.

You said to create two version tables, but I don't see what part the first of the two tables plays, as it's not referenced in the measures anywhere (although it does have an active relationship). Can you clarify what it's doing?

Also, when I saw you'd stated that two version tables should be used, I'd presumed these could be used as sources for individual slicer lists, allowing one version to be picked on one and another on the other. However, because of how the data model works, if you choose one version from one list, it filters down the entire table, meaning nothing then shows against the comparison measure.

Hope I've explained myself clearly enough. I'm going to keep having a play myself to see if I can get something to work, but any further assistance you can offer is appreciated.

Cheers,

Matty
 
Upvote 0
Hi Matt,

Thanks for replying.

I kind of understand the logic, but I'm struggling to implement something that does what I need.

You said to create two version tables, but I don't see what part the first of the two tables plays, as it's not referenced in the measures anywhere (although it does have an active relationship). Can you clarify what it's doing?

Also, when I saw you'd stated that two version tables should be used, I'd presumed these could be used as sources for individual slicer lists, allowing one version to be picked on one and another on the other. However, because of how the data model works, if you choose one version from one list, it filters down the entire table, meaning nothing then shows against the comparison measure.

Hope I've explained myself clearly enough. I'm going to keep having a play myself to see if I can get something to work, but any further assistance you can offer is appreciated.

Cheers,

Matty

Managed to sort this by adding ALL to the 'total comparison forecast' formula.

Cheers,

Matty
 
Upvote 0
I'm trying to learn from this useful tip but can't make it work. I guess the first problem I have is trying to load the same table twice , any suggestions?

Peter

Hi Peter,

Using Power BI, you can create a blank query in Power Query and then use M code as follows:

Code:
let
    DataVersions = Table.SelectColumns(FactData,{"Version"}),
    #"Removed Duplicates" = Table.Distinct(DataVersions),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Version", "Version 1"}})
in
    #"Renamed Columns"

This takes a single column from the main fact table and creates a distinct list of values from it. You can repeat this process as many times as you wish to create direct copies of the same table should your needs require it.

Same applies in Excel if using Power Query.

Cheers,

Matty
 
Upvote 0
Thanks Matty,
That's what I had been trying but when it didn't work I was struggling; main problem was that connecting two to one doesn't deactivate the second relationship. I think you've nailed with the use of ALL. It's a bit late so will try tomorrow.
Peter
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top