Forum Discussion
Matrix Total Line Representation
I'm currently working on a PowerBI project where I've constructed a matrix representing various categories of our unique identifiers, denoted by numbers displayed on the far left. Within this matrix, I've aggregated several metrics corresponding to these identifiers.
However, here's the challenge I'm encountering: While the matrix displays only a subset of the unique identifiers (specifically 7 out of 12 - 30/60/90/130/140/170/180), I want the total line to encompass the entire population of categories, not just the ones filtered in the matrix.
To illustrate further, I aim to have the total line represent all categories for the metrics displayed, irrespective of whether they are filtered within the matrix or not. Is it possible within PowerBI to configure the total line in a matrix to include the entire population of categories, rather than being limited to the filtered subset?
I appreciate the help!
gmat See attached PBIX.
Measure = IF( HASONEVALUE('Table'[Class]), SUM('Table'[Revenue]), SUMX(ALL('Table'), [Revenue]))
7 Replies
gmat You should be able to get around this using ALL and this method. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Greg_Deckler , I am not sure I am following. The links you have given do not specifically answer my question. I am truly trying to figure out if I can total the unit count of the whole population in the totals column while still having the matrix filtered on a select few categories of the population.
gmat Correct, and I am saying you would do that using ALL to get rid of the filters and you would want to use HASONEVALUE to determine if you are in a total row or not. Without more details, sample data, etc. it's next to impossible to be specific as to the exact solution. All I can answer is that, yes, it should be possible and here is some guidance on how to do it.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
