Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hashari
Helper I
Helper I

Variance Calculation Between Different 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 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'):

ScenarioA.png

 

 

 

 

 

 

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:

 

ScenarioB.png

 

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 

4 REPLIES 4
AllisonKennedy
Super User
Super User

@hashari 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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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 LeftCurrent Situation if Single Entry Selected on LeftDesired Outcome when Single Entry Selected on LeftDesired Outcome when Single Entry Selected on Left

@hashari  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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.