Forum Discussion
Helper IIIRow Sub Total equals is calculated
OK, taking a look back at the original post, perhaps something like:
Measure 2 = VAR __Table = ADDCOLUMNS( SUMMARIZE( 'Table 3', [Group], [Attribute], "__Measure",[Measure] ), "__IncludeInTotals",SEARCH("%",[Attribute],,-1) ) RETURN IF( HASONEVALUE('Table 3'[Attribute]), [Measure], SUMX(FILTER(__Table,[__IncludeInTotals] = -1),[__Measure]) )
Community ChampionGoing to depend on how you are subtotaling, but:
Measure =
IF(
HASONEVALUE('Table 3'[Group]),
SUMX (
DISTINCT ( 'Table 3'[Attributes] ),
SWITCH (
'Table 3'[Attributes],
"Attribute5", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute1" )
- CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute2" )
- CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute3" ),
var a = 'Table 3'[Attributes] return
CALCULATE ( SUM ( 'Table'[Value] ),'Table'[Attributes]=a)
)
),
"Attribute5"
)
Helper IIICan i do the same for the global column total ?
Like instead of having "Attribute5" there i'd like to have 1142(which is 239+903)
- Greg_Deckler
Yes, that's more along the lines of 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- Fragan
As far as i understand, your post is to filter the total on some value, but not excluding a Row Value from Totals, can you pelase check OP i edited OP with more details
- Greg_Deckler
OK, what I don't understand is this part "and every Attribute containing % in the future". Are you saying that in the future you may have other attributes that have a percent symbol in them and you want those excluded?
Meanwhile, you may want to take a look at MM3TR, it was designed for more complex scenarios than just grand totals not being correct but the concept is the same, you create a table variable that does a summarize or group by exactly the way you have it laid out in your visual, in your case, you would then filter out anything that SEARCH for % came back true or non-blank, etc. and then you would aggregate over that table using something like SUMX.