Forum Discussion
Matrix Total Line Representation
gmat See attached PBIX.
Measure = IF( HASONEVALUE('Table'[Class]), SUM('Table'[Revenue]), SUMX(ALL('Table'), [Revenue]))
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!