Forum Discussion
Helper VMatrix Visual - Prevent Drill Down from one item
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.
Solution SageYou 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.
shaunwilksTerrific 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.
v-jiascu-msftMicrosoft Employee
- 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.
- Seward12533This 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.
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.