Matrix Totals with Switch measure and ISINSCOPE

[DeanJ]

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)

[Harsh]

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)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[Harsh]

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)

[EnterpriseDNA]

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!

[DeanJ]

Thanks @Harsh!

[Harsh]

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