cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fragan
Helper III
Helper III

Row Sub Total equals is calculated

Hey,

 

I have this table :

LdjDX

 

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 :

F2fQS

 

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

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

Can i do the same for the global column total ?image.png

Like instead of having "Attribute5" there i'd like to have 1142(which is 239+903)

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

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-...

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

 

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

 

Im checking the link you just sent rn

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

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

 

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors