Variance Calculation Between Different 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 is what should be displayed in VARIANCES.

This works for matching rows between "GROUP 1" & "GROUP 2", so the math works as in the example screenshot as long as we are comparing like Rows (such as 'Div 2' vs 'Div 2'):

The problem I am having now is that the 3rd visual (VARIANCES) is calculating the difference at the row level only when there is a corresponding row of data (e.g., Div 2 values minus Div 2 values).

But, the solve I am now looking for is how to calculate VARIANCE (GROUP 1 minus GROUP 2) values for non-corresponding rows.

As illustrated in the below screenshot in Red Text:

Here is a link to the pbix file on Google Drive: https://drive.google.com/file/d/1HBmQW7WFzcXWToB6HRDDSSRiMG5KWDWW/view?usp=sharing

Thank you to All in Advance!!
Also if you have a chance @parry2k 

[AllisonKennedy]

@Anonymous I just had a look at your pbix file - lots of manual measure calculations in there, so might take some time to sort out the best way to do this. 

I don't understand why you want to compare Div 2 from group 1 to only Div 5 from Group 2 - can you filter the file down as per your screenshot and then give ALL the numbers you want for all rows as a drawing/excel screenshot/table please so we understand the full picture.

Also, you will need to have a clear ruleset to follow, so how does Power BI know that div 2 and div 5 are comparable but div 2 and div 4 are not?

[Anonymous]

Hi @AllisonKennedy  -- sorry, I shouldn't have been so specific in that example/screenshot.

If there is only the single selection on the left, then I want to compare what's on the left side to EACH of the items on the right side per the "VARIANCES" matrix visual.

So Div 2 would remain fixed in this scenario on left, and VARIANCES visual would display differences for:
a) Div 2 on Left vs Div 2 on Right

b) Div 2 on Left vs Div 4 on Right

c) Div 2 on Left vs Div 5 on Right

d) Div 2 on Left vs Div 7 on Right

e) Div 2 on Left vs Div 10 on Right

Also, if the user Drills into Div on Left they hit the "Reg"
And would want to maintain the same type of comparisons -- If its single value on left, compare it to all values on right.

Hopefully this screenshot and sample desired Excel outcome helps to better illustrate:
Excel File on Google Drive: https://drive.google.com/file/d/1ROdBLpO4tv9C3Cy1NkjXssHNwrtrMsXJ/view?usp=sharing

Current Situation if Single Entry Selected on LeftDesired Outcome when Single Entry Selected on Left

[AllisonKennedy]

@Anonymous  Is this only if there is a single value on left? 

If so, then you should be able to achieve by adding an IF statement to each measure

IF(HASONEVALUE(Group1[Div], ....

Then use ALLSELECTED with the right combination of column/tables to clear the filters that get applied in the row of the matrix. 

Does that make any sense without going into specific details?

[Anonymous]

@AllisonKennedy -- I may be able to make that work, but honestly feel a bit out of my element.
Could you post an example of 1 to get the ball rolling for me please?

Also, as my Left Side (or Right Side) can drill down to 4 levels:
Div -> Reg -> Dis -> Sto ... what does that do to this:

IF(HASONEVALUE(Group1[Div], ....

Because you have "Div" only in there -- how to account for all lower-levels as a user may want to drill?

Additionally, what troubles me further is, what If there is more than 1 selection on the Left?
For example, user Decides to keep Div 2, 4, 5 selected on Left, and all 5 (Div 2, 4, 5, 7, 10) on the Right?
The Desired Outcome here would be to compare in Aggregate all of the Selections on the Left (so, TOTAL of Left) to EACH of the Items on the Right -- have no clue how to get there though.