Forum Discussion

gomezc73's avatar
gomezc73
Helper V
1 year ago
Solved

Working with Matrix with different levels..

Hi Experts,

 

   I am working with a data that have diferents level (Subcategories) to show the amounts in a matrix.

 

   By example, in a category only have two levels, other category 5 levels, etc.

 

  In the matrix I should show all the levels, but I know that the amounts in some categories are at level 2, and in others I should expand up to 5 levels to get the amounts.

 

  Is it possible in PBI to control that I can expand the levels only up to where the amounts are?

 

  By example:

 

   If i need see the amount of "Cost" i only need expand the matrix until level3 to see the amounts.

   If i need see the amount of  purchasing/China i need expand until the level 5 to see the amounts.

    

Level1Level2level3level4level5codeAmount
Total CompanyAccountingCost  A01       800.00
Total CompanyAccountingAudit  A02       992.00
Total CompanyAccountingAudit  A03    1,230.08
Total CompanyMarketingSocial networksInstagram A04    1,525.30
Total CompanyMarketingSocial networksTwitter A05    1,891.37
Total CompanyMarketingOther  A06    2,345.30
Total CompanyPurchasingLocal  A07    2,908.17
Total CompanyPurchasingInternationalAsiaChinaA08    3,606.13
Total CompanyPurchasingInternationalAsiaJapanA09    4,471.61
Total CompanyPurchasingInternationalEurope A10    5,544.79
Total CompanyManufacturing   A11    6,321.06
Total CompanyManufacturing   A12    7,206.01
Total CompanyManufacturing   A13    8,214.85
Total CompanyManufacturing   A14    9,364.93
Total CompanySalesFloridaMiami A15  10,676.02
Total CompanySalesFloridaMiami A16    6,875.54
Total CompanySalesFloridaOrlando A17    7,838.12
Total CompanySalesFloridaOrlando A18    8,935.45
Total CompanySalesGeorgia  A19  10,186.42
Total CompanySalesGeorgia  A20  11,612.51
Total CompanySalesGeorgia  A21  14,399.52

 

Any help wil be appreciate,

 

regards

 

Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    hello gomezc73 

     

    something like this?

    however, there are couple blank row because some rows only have value up to Level 2.

     

    i am not sure but to create a matrix as your screenshot seem not possible at this moment.

    there are levels that dont have value, for example, there are three level in Sales while there are only one level in Accounting.

    So you would have a blank value in Accounting if you added until level 3 in row matrix to show value in Sale.

    Visual filter seems not work as well because your blank value are placed differently in level.

     

    Thank you.

  • gomezc73's avatar
    gomezc73
    1 year ago
     
     
    clear
     
     
     
     
     
     
     
     
     
     
     
    183 / 5.000
     

    Resultados de traducción

    Resultado de traducción

    Exactly, that's the same result I am getting, and that's why I asked if any experts know a trick to prevent those blank levels from appearing. Thanks for the clarification.

6 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello gomezc73 

     

    it should be possible using bookmark or drillthrough (although it need some reworks to do).

     

    here i create a simple example using bookmark.

     

    Hope this will help.

    Thank you.

    • gomezc73's avatar
      gomezc73
      Helper V
      1 year ago

      Hi,

       

        Thank you for your early response.

       

         Is not possible to have a matrix to show the data in this way?

       

      • Irwan's avatar
        Irwan
        Super User
        1 year ago

        hello gomezc73 

         

        something like this?

        however, there are couple blank row because some rows only have value up to Level 2.

         

        i am not sure but to create a matrix as your screenshot seem not possible at this moment.

        there are levels that dont have value, for example, there are three level in Sales while there are only one level in Accounting.

        So you would have a blank value in Accounting if you added until level 3 in row matrix to show value in Sale.

        Visual filter seems not work as well because your blank value are placed differently in level.

         

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi gomezc73,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community.
    And Thanks to Irwan  for promt and helpful response

     

    Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

    If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

     

    Best regards,

    Prasanna Kumar

    • gomezc73's avatar
      gomezc73
      Helper V
      1 year ago

      Hi,

       

      Thank you for the response, i was thinking if is possible to show a matrix like this: