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
Anonymous
Not applicable

Subtotal line between groups, not for all of them.

Hey there! =D

 

i just arrived at this company, witch they use tons of Excel sheets with dynamic tables, "vlookup" etc...

Anyway, the thing is i need to know how to create this kind of subtotal line between groups in a Power BI matrix/table, its like "Subtotal before contingency & tax benefits" followed by the next 2 lines with contingency & taxes, and only then the grand total, as in this excel table:
note: i'm using Matrix, not Table. With hierarchy in some groups, not all of them.
Subtotal.png

3 REPLIES 3
MFelix
Super User
Super User

Hi @Anonymous,

Yu need to create a group column to add to your matrix with all the accounts and subaccounts.

Something similar to.

Level 1
Investimentos
Contingencias
Beneficio fiscal
Level 2
Investimentos indiretos
Investimentos diretos
Level 3
Engenharia
Owners team....
Level 4
Infraestrutura...

On the fields where you just want the level 1 for example contingência do not add level 2 3 and 4.

Now use those levels for your matrix.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix 

Thanks a lot, it helped me a lot here. But...

There are 3 points to be clarified yet. 1st is the most important to me right now

1º - How would i bring the Subtotal Row to the bottom of the table right before "CONTINGÊNCIA" row? - with that answer, i get my solution

Subtotal2.png

 

2º - I need to order the rows by a certain sequence, as in the print (1º, 2º, 3º...), i'll try some measures/DAX tricks with RANKX, IF statements, VAR etc... And order the Level 3 Column by this Measure... Will it work? Cuz there are some "null" cells in this column

3º - Now i got some issues coloring specific Rows by its Level, but i'll try something like this tutorial https://www.youtube.com/watch?v=TwUKVrY8KE0

This is how i'm using the columns. The "blank" cells is "null"

subtotal3.png

Hi @Anonymous ,

 

Yesterday I wans't in my computer and today got to look at your information believe the best way is to create a parent child hierarchies.

 

Check the documentation below with a full explanation on how it works and how it can be implemented.

 

https://www.daxpatterns.com/parent-child-hierarchies/


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.