Creating a matrix with a custom subtotal and grand total

[Anonymous]

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!

[Anonymous]

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 ☹️

[Anonymous · Accepted solution]

Hi @Anonymous 

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

[Daniel29195]

@Anonymous 

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