Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
@gmat See attached PBIX.
Measure = IF( HASONEVALUE('Table'[Class]), SUM('Table'[Revenue]), SUMX(ALL('Table'), [Revenue]))
@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/n4TYhF2ARe8
@Greg_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.
Hi @Greg_Deckler , apologies for the lack of detail in my post. Below is a dataset that I have used to create the matrix in the following screenshot. What I am trying to do is keep the current selection of classes 30/40/50 but have the sum of units in the matrix = to total sum of all units (1450) and total sum of revenue in the matrix to be total sum of all revenue ($330,000).
Class | Units | Revenue |
20 | 100 | $6,000 |
30 | 110 | $12,000 |
40 | 120 | $18,000 |
50 | 130 | $24,000 |
60 | 140 | $30,000 |
70 | 150 | $36,000 |
80 | 160 | $42,000 |
90 | 170 | $48,000 |
100 | 180 | $54,000 |
120 | 190 | $60,000 |
@gmat See attached PBIX.
Measure = IF( HASONEVALUE('Table'[Class]), SUM('Table'[Revenue]), SUMX(ALL('Table'), [Revenue]))
Great, thanks so much. I had to tweak the formula a bit to take into account all of the other filters but it worked!