Which DAX function can I use to group certain accounts to show sub totals of groupings

[Mattm1989]

Hi

I want to group the below data so it shows sub groupings for gross profit and gross operating profit using DAX:

I would like to show Revenue and Cost of sale under "Gross Profit" and Delivery and Operations under Gross Operating Profit so it looks something like the below:

Please can someone shed some light on how I can acheive this?

Thanks

Matt

[Greg_Deckler · Accepted solution]

@Mattm1989 You generally use a disconnected table for this, similar to this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

[Anonymous · Accepted solution]

Your solution is so great Greg_Deckler 

Hi, @Mattm1989 

According to your description and the screenshots shown, in addition to using the Super User method to achieve this goal, you can also refer to the following method:

Since the matrix implements a hierarchical structure, you have the following levels:

If not, you can increase these calculated columns using the IF condition or the switch function. Set in the matrix as follows:

If you want to change the calculation method for a certain level, you can do the following:

Measure = 
IF(NOT HASONEFILTER('Table'[Category1]) && SELECTEDVALUE('Table'[Category2]) = "Gross Profits",10000,SUM('Table'[Amount]))

If you want to modify calculations at multiple levels at the same time:

The above and the documentation provided by Super User are the current viable methods. In Power BI, you can't add rows to a matrix at will. This is the same principle as pivot tables in Excel.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mattm1989]

Surely there must be another way? This seems fairly complex for a beginner to try and figure out! 😕

[Greg_Deckler · Accepted solution]

@Mattm1989 You generally use a disconnected table for this, similar to this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community