Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
@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?
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 Left
Desired Outcome when Single Entry Selected on Left
@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?
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
72 | |
68 | |
41 | |
35 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |