how to set matrix row expand enable/disable conditionally financial statement

[samantha75]

Hi Everyone

I have a P&L table as below with some categories like Net Sales and Gross Profit that should show only one row without expand. For example Net Sales below should not expand and show one row

The format is driven by an excel template as below and I intend to include a column for expand

What is the best way to achieve the conditional expand?

[v-jtian-msft · Accepted solution]

Hi,@samantha75 . I am glad to help you.
According to your description
You want to specify that a specific row stored in the matrix has a drill-down effect (putting other columns under that column hierarchy)
while some other fields in that column do not have a drill-down effect.
Unfortunately, with the default matrix provided by power BI, setting a hierarchical drill-down for a field has an effect on all the data in that column, so you can't disable the drill-down for certain values that meet the conditions individually.
Here is my alternative
You can write a new measure to judge the matrix hierarchy to control the displayed value, according to your description, you don't want to expand the hierarchical data under the Net Invoice Sales and Net Sales columns, so you can use the Switch/IF function to judge, when the current row is filtered by the hierarchical fields under these two columns, display blank, otherwise it will be normal. Display the original value
You need to use the HASONEVALUE() function or ISINSCPOE() function to determine the level of the matrix.
Here is my test, hope it can help you.

M_Base Amount = 
SWITCH(
    TRUE(),
    HASONEVALUE('Report'[level_]) && 
    (MAX('Report'[ReportSection]) = "Net Invoice Sales" || MAX('Report'[ReportSection]) = "Net Sales"), 
    BLANK(),
    SUM('Report'[value_])
)

Here is my test data:

It should be noted that the above is my test data based on the screenshots you provided, you need to create a new measure based on the data in your actual environment, not directly use the measure I provided, because everyone's computing environment is inconsistent, and the measure will be affected by different computing environments to display different values.
Because everyone's computing environment is not the same, and the measure will be affected by different computing environment to display different values.

Since the default matrix provided by power BI does not support the setting of some fields to prohibit the expansion of the drill-down function, you can go to the mall to look for a suitable custom visual, to see if there is a custom matrix visual available to meet your needs.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.