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.
Helper VTerrific 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.
- Seward12533Not 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/42395
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.
- 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.