Matrix Visual - Prevent Drill Down from one item

[shaunwilks]

Prevent drill down from one item within the matrix

I have seen this done so I know its possible. There is an example on the web but are worried about publicising that link without the developers consent.

Im putting together a financial report- Profit and Loss.  Most Profit and loss' show INCOME - COGS (Direct Expenses) = Gross Operating Profit. Less Operating Expenses = Net Profit.

In my visual I have no problem showing Income, Direct Expenses and Operating Expenses.

Its the Gross Operating Profit and the Net Profit I want to show whicha re the totals of the rows above it.

What I want to do is insert a row after the Income and Direct Expenses that is "Gross Profit". It would be the sum of the lines above it in the matrix and as there is no other level there would be no drill through.

I have seen this done and even it it was done somehow in the data schema I dont understand how the Gross Profit line could hold a value, but the drill down on the Gross Profit item be disabled and drill to nothing while the other groups at the same level all drill down.

[Seward12533 · Accepted solution]

You need to modify your meaures so you drill down under the categories you don't want to display details under nothing is displayed. Some sample data and a picture of what your trying to do would help but here is a sample of what I'm talking about - See first sheet "Block Drill Down"

Using a combinatin of ISFILTERED and HASONE VALUE you block the calcalcution in certain situations when you return a NULL the row will not be displayed

Avg Block Berries = IF(NOT(ISFILTERED(BerryBuyers[Buyer])),[Average Revenue per Buyer],IF(FIRSTNONBLANK(Types[ProductType],1)<>"Berries",[Average Revenue per Buyer]))

The table on the left uses the RAW measure and you see average sales for individual Berry Buyers. The one on the right uses the blocking meausure and no buyer details under Berries are displayed.  

[stitch_es]

Hi - I know this is an old post, but hoping you can help me based on your response above.  I have a matrix that has multiple rows - but only a certain part of the matrix has data to the last level - the others have no data.  Is there a way to "turn off" the drill down +/- for the rest of the matrix?  

[shaunwilks]

Hello

Yes I definitely go this working back in the day and it is hard to recall. I think its all mentioned in the comments below.

The summary rows I used s summary table where I built a table in DAX and just calculated the totals myself for the group sections I didnt want to have exploded.

So there was a table with a list of all the categories I had in the report.

For those that wanted a dirll down of records I joined to the fact table of data.

For those I didnt want to drill down into the DAX did the summarise and calculations for me. 

As there were no records in the fact table for that category it never drilled down. 

[shaunwilks]

Terrific thanks - it shows me how to set NULL in the first row of what I planned to do.

The other part to the request may be a little harder and differs to your fruit example whete the values are only ever part of 1 of the Types.

I am wanting essentially to insert a group section that includes other values.

I will try with this simply example to explain what I wish to do. Take this as a fact table...

nbr........type..............value

123.......Invoice....... 250

456.......Credit....... -100

457.......Credit....... -100

124.......Invoice....... 200

987.......Payment.......-300

125.......Invoice....... 280

I wish to show it inside the matrix as below.

Invoices (Total)............730

Credit.............................-200

Gross Amount ........530

Payments......................-300

Amount Owed ........230

I want the bolded subtotals inserted into the matrix at the same level as Invoices and Credits.

I want those subtotals not to be able to be drilled down on (which you have illustrated for me -thx)

But I want to drill down in the Invoices, Creidts and Payments sections.

I have seen in a sample online (dont have the pbix unfortunaley) that this has been done.

There are no Grand Totals in the matrix but they have defined their own subtotals to be inserted as groups within the dataset.

Those subtotals cannot ever be drilled down on.

[Seward12533]

This is not easy to format the way you want in PowerBI if you want the details in the same matrix. Try the matrix visual with Show on Rows for the Columns enabled and just show summary measures.

You may want to look at power pivot in exc l where you can take two pivot tables convert to formulas and combine unrelated data that stays Linked to the source queries and slicers. (Data model is power pivot is almost the same except for some newer Dax all of which you can code around with more complex Dax.

[v-jiascu-msft]

Hi @shaunwilks,

Can you share a snapshot? 

Best Regards,

Dale

[shaunwilks]

As requested below is an example of what I am trying to achieve in the matrix.

I have so many instances of where these sort of custom subtotals would be handy inside tables and grids.

Especially in financial or bank statement like reporting.

Revenue, COGS and Gross Profit are all at the same level in the matrix and same group.

Revenue and COGS collate the rows that are grouped by those type.

Gross Profit cannot be drilled down on - but its the total of the Revenue and COGS records.

[Seward12533]

Not really in PBI wheelhouse. Can do lots of amazing things. If the groups are standard you can write measures for each value you want in a row and then build a matrix and put values on rows. But it’s very clumsy and formatting of the row headers where some are indented etc is hard if not impossible. You can do bold etc by playing with the field formatting.

Should be possible in the POWER stack soon with SSRS once it can use PBI models asa a source, but not really in PowerBI. You can get much closer in PowerPivot in Excel.

When we transitioned to PowerBI for our financial reporting we adopted a different paradigm for displaying the data. This is similar to what we did
https://powerbi.microsoft.com/en-us/partner-showcase/sensdat-p-l-and-balance-sheet-solution/
https://m.youtube.com/watch?v=Mag6YWIvlwg

See this related discussion https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223
https://community.powerbi.com/t5/Desktop/General-Ledger-to-Balance-Sheet-Financial-Statement/td-p/42...

Fundamentally comes down to using the right tool for the Job and PBI might not be it. But then good news is excel can use PowerPivot and even data from published PBI models if you have the service.

I know the guys at powerpivotpro do a lot of financial data reporting and have developed solutions in PBI but may require shift in the way you report on the numbers.

[shaunwilks]

Terrific links and discussion - much appreciated.

I have been rather successful achieving all I wanted to in terms of getting the groww Profit and total proift totals into the matrix.

Effectively..

- Created a new table with the report sections (ensure was not linekd to fact tables)

- Wrote a SUMMARISE query on the fact table that summed the relevant account types for each of the Report Sections in the table above.

When I the report sections field and summarise measure into the visual everything looks perfect.

Where I am at with it is the ability to stop the drill down in these items.

I have your excellant Berries example and when I play around in that solution Im able to stop the drill down in your related dataset.

But in mine I still have issues. I either have 

1) The item is hidden totally from the matrix and doesnt just haven drill down disabled.

2) OR If I flag the report section field to "Show Items with no Data" = Yes, the section shows but then the drill down shows also.

Have you come across these sorts of things with your blocking drill down formula in the past ?

Am sure Ive fallen into a trap I shouldnt have.

[Seward12533]

I’m sure your close. Probably in the logic. May need to add some ALL filters. What typically do to troubleshoot is write some measures that duplicate the individual Boolean checks and display them to deconstruct what is going on.

[shaunwilks]

I have it working - really not sure what I did tbh - There is a combination of <> 0.00 and show items with no data so you have been pretty spot on all the way along.

Cant thankyou for your help enough.

[paulomeneses]

Hey, guys. Good am.

I've exactly the same issue. Could you please help me out in detailing the solution?

I've achieved the part of turning "Gross Profit" blank when drilling down, but I would like to turn it visible, but not detailed.

Thanks in advance!