Show Only subtotals in matrix and leave blank lower levels.

[n3ptune]

I have a matrix table with a field coming from dimension table and other 2 fields coming from fact table.

I want a measure which will be visible only in subtotals and grand total level .

When i drill down to lower levels i want them to be empty like the following

Dept     Revenue

Market             100000

    John                blank

       Maria               blank

Bar                    200000

    Smith              blank

       David               blank

I have tried if isinscope but does not work.

[mohammedadnant]

Hi @n3ptune 

please try isinscope DAX as explained in this video

https://youtu.be/926alfqFhMs

Thanks & Regards,

Mohammed Adnan

Learn Power Platform from my YouTube Channel TAIK18 click on the name

[jaideepnema · Accepted solution]

Hi @n3ptune ,

I again tried to replicate with the following data:

In this scenario for Category A we have only one sub category A1. I have created the following measure:

Value_Total = IF(ISFILTERED('Table'[Level 2]),BLANK(),sum('Table'[Value]))

Which gives me the following output:

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[amitchandak]

@n3ptune , Can you share the formula /measure?

Assume revenue is  measure 

You should try like a new measure like

if(isinscope(Table[Dept]), [revenue], blank())

[jaideepnema]

Hi @n3ptune ,
I tried replicating the scenario. Here is the data used in this case:

I created another measure as shown below:

Value_Total = IF(HASONEVALUE('Table'[Level2]),BLANK(),sum('Table'[Value]))

This gives the following result:

 Hope that helps !!

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[n3ptune]

Yes this is what i tried also as simple example and it works but in my case there is a category which has ONLY one value as subcategory and this is where its breaking.