Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Variance Calculation Between Matrix Visuals

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 th...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
    parry2k
    5 years ago

    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.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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%