Forum Discussion

shaunwilks's avatar
shaunwilks
Icon for Helper V rankHelper V
8 years ago
Solved

Matrix Visual - Prevent Drill Down from one item

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.

Need Help
  • Seward12533's avatar
    Seward12533
    8 years ago

    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.  

     

12 Replies

  • Seward12533's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.  

     

    • shaunwilks's avatar
      shaunwilks
      Icon for Helper V rankHelper V
      8 years ago

      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.

    • stitch_es's avatar
      stitch_es
      Frequent Visitor
      2 years ago

      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's avatar
        shaunwilks
        Icon for Helper V rankHelper V
        2 years ago

        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.