Variance Calculation Between Matrix Visuals

[Anonymous]

Hi,

I have 2 Matrix Visuals that the user can make filtered selections against: "GROUP 1" and "GROUP 2".

Below those is a 3rd visual, "VARIANCES", this is meant to show the difference between the 2 above Groups. 

So, GROUP 1 values - GROUP 2 values.

The problem I am having is that the 3rd visual (VARIANCES) is not calculating the difference at the row level, but is rather using the Totals in any given column.

As an example, per screenshot below:
GROUP 1, Row 1, Metric 1 has a value of -1.56%

GROUP 2, Row 1, Metric 1 has a value of -4.53%

However:

VARIANCE, Row 1, Metric 1 shows a value of 0.51% <-- this variance actually comes from -1.56% - -2.06% = 0.51%.

So, it's using the entire total of Metric 1 instead of just the corresponding row ("10-HIGHLY CONSUMABLES" on the left vs "10-HIGHLY CONSUMABLES" on the right).

What I would need the calculation to be is -1.56% - -4.53% = 2.97%

Update/Edit: A sample version of the file has been added to Google Drive, link here: 

https://drive.google.com/file/d/1olKwMdOdbXJKe9-c3VzLN37QrFCZtr4W/view?usp=sharing

[parry2k · Accepted solution]

@Anonymous solution attached, you have to have common dimension table to compare and I did this using DAX, you can always do that in the backend or in PQ.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous solution attached, see the duplicate page, you need to use columns from common dimension SM0 and also changed the relationship to "one to many" between sm0 and sm1 and sm2

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k - I find myself in the habit of thanking you -- you did it again!

Wouldn't have considered taking the route you used, so again, another brilliant solve!

[parry2k · Accepted solution]

@Anonymous solution attached, you have to have common dimension table to compare and I did this using DAX, you can always do that in the backend or in PQ.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous it is not easy to provide the solution looking at the screenshots. Better to provide a sample pbix file using one drive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[Anonymous]

@parry2k -- That was a brilliant solution!

I am trying to adapt it to one additional PBIX file that I have that is very similarly structured but with a few additional metrics. 

The difference is that previous "Sample1" file shared the same data source (tables 'CS1' and 'CS2') on both left and right side for a single set of metrics, which I saw the common dimension table and supporting DAX you came up with.
File "Sample2" (per Google Drive Link below) on the other hand, has a few additional data sources that come from other tables (not just tables "CS1", "CS2" as before). 

Would you be able to take a look at this one as well?

https://drive.google.com/file/d/1aAyypgMGOKZINexO1X5Wm0L7RQ-1x_Uf/view?usp=sharing

Similar to before, as an example:

GROUP 1, Row 1, Metric 2b has a value of -1.79%

GROUP 2, Row 1, Metric 2b has a value of -5.19%

However:

VARIANCE, Row 1, Metric 2b shows a value of 6.16% <-- this variance actually comes from -1.79% - -7.94% = 6.16%.

So, it's using the entire total of Metric 2b instead of just the corresponding row ("Div 2" on the left vs "Div 2" on the right).

What I would need the calculation to be is -1.79% - -5.19% = 3.4%

[Anonymous]

@parry2k and others, I have added a sample version of the file to Google Drive:

https://drive.google.com/file/d/1olKwMdOdbXJKe9-c3VzLN37QrFCZtr4W/view?usp=sharing

Please let me know if this share does not work for any reason, happy to revist it and change settings if need be.