cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

## Creating a matrix with a custom subtotal and grand total

Hi,

I am looking to create a matrix table in Power BI that would look like this:

I have written measures in DAX that give me the total and grand total, but when I try to add the measures to the matrix, they are added as columns, not rows.

Will someone please let me know how I may achieve a custom subtotal and grand total? I am a DAX newbie.

Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @tt3

You can refer to the following solution.

Sample data

Sample measure

``Sum = CALCULATE(SUM('Table'[Value]))``

1.Crate a new table

2.Create a measure

``````MEASURE =
IF (
SELECTEDVALUE ( 'Table (2)'[Type] ) <> "Total",
CALCULATE (
[Sum],
FILTER (
'Table',
'Table'[Fruit]
IN VALUES ( 'Table (2)'[Type] )
&& 'Table'[Quarter] IN VALUES ( 'Table'[Quarter] )
)
),
CALCULATE (
[Sum],
FILTER (
ALLSELECTED ( 'Table' ),
[Fruit] <> "Carrots"
&& [Quarter] IN VALUES ( 'Table'[Quarter] )
)
)
)
``````

3.Then put the following field to matrix

Output

Best Regards!

Yolo Zhu

3 REPLIES 3
Microsoft Employee

Thank you for your suggestion to use isinscope. I don't think I will be able to use it though as it doesn't seem to be compatible with RLS rules. ISINSCOPE function (DAX) - DAX | Microsoft Learn ☹️

Community Support

Hi @tt3

You can refer to the following solution.

Sample data

Sample measure

``Sum = CALCULATE(SUM('Table'[Value]))``

1.Crate a new table

2.Create a measure

``````MEASURE =
IF (
SELECTEDVALUE ( 'Table (2)'[Type] ) <> "Total",
CALCULATE (
[Sum],
FILTER (
'Table',
'Table'[Fruit]
IN VALUES ( 'Table (2)'[Type] )
&& 'Table'[Quarter] IN VALUES ( 'Table'[Quarter] )
)
),
CALCULATE (
[Sum],
FILTER (
ALLSELECTED ( 'Table' ),
[Fruit] <> "Carrots"
&& [Quarter] IN VALUES ( 'Table'[Quarter] )
)
)
)
``````

3.Then put the following field to matrix

Output

Best Regards!

Yolo Zhu

Super User

you want your totals and subtotals to be  custom ?

you can use the isinscope ( valid for matrix visual )  function. this function lets you control the the calculation base on the level you on .

measure pattern :

measure =

switch(

true() ,

isinscope( tbl_name[col_name] ), calc_1 ,

isinscope( tbl_name[col_name_1] ), calc_2 ,

....

im not sure if the below would help ,

did you consider switching values to rows ? ( only available for matrix visual )

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors