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

[Matty]

Hi Team,

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

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

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Version[/TH]
[TH]Month[/TH]
[TH]Forecast[/TH]
[/TR]
[TR]
[TD]A[/TD]
[TD]Jan[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Feb[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Mar[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Apr[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]May[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Jun[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Jul[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Aug[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sep[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Oct[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Nov[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Dec[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jan[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Feb[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Mar[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Apr[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]May[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jun[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jul[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Aug[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Sep[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Oct[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Nov[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Dec[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Jan[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Feb[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Mar[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Apr[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]May[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Jun[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Jul[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Aug[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Sep[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Oct[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Nov[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Dec[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Matty]

Bump...

 

[Matt Allington]

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]

 

[peter789]

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

 

[Matty]

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

 

[Matty]

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

 

[Matty]

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:

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

 

[peter789]

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