Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.