Matrix with multiple measures - disable drilldown only for one measure

[Anonymous]

Hello,

I have a matrix with three measures in the values tab and I need to disable the drill down for one of the measures. Is this possible? Thank you.

[parry2k]

@Anonymous not sure what you mean? You mean when you drilling into the rows, at next level you don't want to see the value for the measure? Provide bit more context. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

[Anonymous]

Hello @parry2k ,

I have attached an image to illustrate my situation. The Budget and Variance measures need to be displayed only at total level, and not split by account, as in the second photo.

[AllisonKennedy]

@Anonymous

Is budget and variance already a MEASURE as you say? If so please can you share the DAX formula you are currently using for these two measures? You could achieve this by focusing on the Measures totals, and add an IF statement into the measure such as :

BudgetNoDrill = IF(HASONEVALUE(Table[Account]), BLANK(), [Budget])

[Anonymous]

Hello @AllisonKennedy, 

Budget and Actual are two different measures. The formula is: B_NetRevenue =
SUMX(
FILTER(FactTable,RELATED(Account[Category3]) = "Net Revenue",[BudgetValue]
)

The actual formula is the same. For the actual I want to display the categories under net revenue, the budget needs to be displayed only at net revenue level. Thank you.

[AllisonKennedy]

@Anonymous
What column are the categories in? You won't be able to hide the columns automatically, but you can make the measure blank. As I mentioned in my previous post, use the column that contains the category, and check that it HASONEVALUE or ISFILTERED and if that's true, display BLANK(), Otherwise do your calculation:

B_NetRevenue =
IF(ISFILTERED(FactTable[CategoryAccount]), BLANK(),
SUMX(
FILTER(FactTable,RELATED(Account[Category3]) = "Net Revenue",[BudgetValue]
)
)

[Anonymous]

Hello @AllisonKennedy ,  the categories are in a column under Account. In the columns part of the matrix I have two columns selected, Category3 and the other small categories which I need to show, but only for Actual, not Budget and Variance. I understand that this is the standard behaviour of the matrix, I will try what you suggested. Thank you.

[AllisonKennedy]

Thanks for the update @Anonymous
Let us know how you get on.