Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tt3
Microsoft Employee
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: 

 

tt3_0-1707950413158.png

 

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

Hi @tt3 

You can refer to the following solution.

Sample data 

vxinruzhumsft_0-1709088221166.png

Sample measure

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

1.Crate a new table

vxinruzhumsft_1-1709088248111.png

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

vxinruzhumsft_2-1709088329302.png

 

Output

vxinruzhumsft_3-1709088340914.png

 

 

 

Best Regards!

Yolo Zhu

View solution in original post

3 REPLIES 3
tt3
Microsoft Employee
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 ☹️

Hi @tt3 

You can refer to the following solution.

Sample data 

vxinruzhumsft_0-1709088221166.png

Sample measure

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

1.Crate a new table

vxinruzhumsft_1-1709088248111.png

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

vxinruzhumsft_2-1709088329302.png

 

Output

vxinruzhumsft_3-1709088340914.png

 

 

 

Best Regards!

Yolo Zhu

Daniel29195
Super User
Super User

@tt3 

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 ) 

Daniel29195_0-1707951025366.png

 

 

 

 

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! 🤠

 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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