cancel
Showing results for
Did you mean:  Helper III

## Row 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

1 ACCEPTED SOLUTION  Super User IV

OK, taking a look back at the original post, perhaps something like:

``````Measure 2 =
VAR __Table =
SUMMARIZE(
'Table 3',
[Group],
[Attribute],
"__Measure",[Measure]
),
"__IncludeInTotals",SEARCH("%",[Attribute],,-1)
)
RETURN
IF(
HASONEVALUE('Table 3'[Attribute]),
[Measure],
SUMX(FILTER(__Table,[__IncludeInTotals] = -1),[__Measure])
)``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

13 REPLIES 13  Super User IV

@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

Proud to be a Super User!  Helper III

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  Super User IV

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"
)``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper III

Can 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)  Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper III

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  Super User IV

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.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper III

Yea i want to exclude each attribute that has % in his name from total

Im checking the link you just sent rn  Super User IV

OK, taking a look back at the original post, perhaps something like:

``````Measure 2 =
VAR __Table =
SUMMARIZE(
'Table 3',
[Group],
[Attribute],
"__Measure",[Measure]
),
"__IncludeInTotals",SEARCH("%",[Attribute],,-1)
)
RETURN
IF(
HASONEVALUE('Table 3'[Attribute]),
[Measure],
SUMX(FILTER(__Table,[__IncludeInTotals] = -1),[__Measure])
)``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper III

@Greg_Deckler Dude you're straight fire lmao. It works, i'll read the docs in order to understand how this works.

Thanks again  Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper III

@Greg_Deckler  should i include this to my mesure or make a new one ??  Super User IV
I personally generally make a second measure. Helps with troubleshooting.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  