Matrix Totals with Switch measure and ISINSCOPE

Hi,
I have a dataset where budget can be set and 2 different levels. I have created a measure to display the budget correctly, but the problem is that the Matrix total is blank. Can you please point me in the right direction?

Level = IF(ISINSCOPE(Budget[Department]),“Department”,
IF( ISINSCOPE(Budget[Product Line]),
“ProductLine”,
IF ( ISINSCOPE(Budget[Division]),
“Division”)))

Budget Switch =
Switch(True(),
[Level]=“Department” && [Dept Budget]>0,[Dept Budget],
[Level]=“ProductLine”,IF([PL Budget]>0,[PL Budget],[Dept Budget]),
[Level]=“Division”,[Division Budget])

Here is a sample file.

Matrix Totals Test.pbix (31.6 KB)

Hello @DeanJ,

Thank You for posting your query onto the Forum.

To fix/achieve the results of the “Grand Totals” just write the smal simple formula as provided below -

``````Budget Switch - Totals =
SUMX(
SUMMARIZE(
Budget,
Budget[Product Line] ,
Budget[Department] ,
"@Totals" ,
[Budget Switch] + [PL Budget] ) ,
[@Totals]
)
``````

Below is the screenshot of the results provided for the reference.

I’m also attaching the working of the PBIX file for the reference as well as providing the link of the post below which was created by our expert Greg which specifically addresses this type of issues.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note: To view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

Matrix Totals Test - Harsh.pbix (31.6 KB)

1 Like

Hello @DeanJ,

Also rather than creating additional formula, you can just revise your original formula as well -

``````Budget Switch - Revised =
SWITCH( TRUE() ,
[Level] = "Department" && [Dept Budget] > 0 , [Dept Budget] ,
[Level] = "ProductLine" , IF( [PL Budget] > 0 , [PL Budget] , [Dept Budget] ) ,
[Division Budget] )
``````

And this will also provides the same results as measure created for the “Grand Totals”. Below is the screenshot of the results provided for the reference -

So you can refer any of the measure according to your requirements.

Hoping this helps you.

Thanks and Warm Regards,
Harsh

Matrix Totals Test - Harsh v2.pbix (31.8 KB)

5 Likes

Hi @DeanJ i @, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Thanks @Harsh!

1 Like

Hello @DeanJ,

You’re Welcome.

I’m glad that I was able to assist you.

And this meme really made me laugh.

Thanks and Warm Regards,
Harsh