Row Sub Total equals is calculated

[Fragan]

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

[amitchandak]

@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]

@amitchandak 

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

[Fragan]

@Greg_Deckler 

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)

[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]

@Greg_Deckler 

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.

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

[Fragan]

@Greg_Deckler 

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

Im checking the link you just sent rn

[Greg_Deckler · Accepted solution]

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])
  )

[Fragan]

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

Thanks again

[Greg_Deckler]

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

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]

@Greg_Deckler  should i include this to my mesure or make a new one ?? 

[Greg_Deckler]

I personally generally make a second measure. Helps with troubleshooting.