Forum Discussion
Helper IIIRow Sub Total equals is calculated
Hey,
I have this table :
And i want to calculate foreach day and foreach group 1and2 % wich is (Attribute1/Attribute2)*100
And i want to include `1and2 %` in Group Subtotal and on total
I created a new table :
I created a new table :
Table3 = UNION(DISTINCT('Table'[Attributes]);{{"1and2 %"}})And i made this mesure to calculate 1and2 %:
Measure =
SUMX (
DISTINCT ( 'Table3'[Attributes] );
SWITCH (
'Table3'[Attributes];
"1and2 %"; IFERROR((CALCULATE ( SUM ( 'Table'[Value] ); 'Table'[Attributes] = "Attribute1" )
/ CALCULATE ( SUM ( 'Table'[Value] ); 'Table'[Attributes] = "Attribute2" ))*100;0);
var a = 'Table3'[Attributes] return
CALCULATE ( SUM ( 'Table'[Value] );'Table'[Attributes]=a)
)
)And this is the result im getting :
I want to exclude 1and2 % (and every Attribute containing % in the future) from group subtotal and the total.
Im really new to PowerBI and not really familiar with Excel like formulas.. Can anyone please help me up with that ?
Regards
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]) )
13 Replies
- Greg_Deckler
Community Champion
Check out MM3TR: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/td-p/411443
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
FraganGreg_Deckler should i include this to my mesure or make a new one ??
- Greg_DecklerI personally generally make a second measure. Helps with troubleshooting.
- amitchandak
Super User
Fragan , I think you need subtotal. You should try have look at this :
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Allexcept will give you that
- Fragan
I dont think i need subtotals, i just want to make Attribute5 the subtotal and not calculate the subtotal again. in other words i want to exclude verything from subtotal and total besides Attribute5
- Greg_Deckler
Going 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" )
