Forum Discussion

zivhimmel's avatar
zivhimmel
Icon for Resolver I rankResolver I
7 years ago
Solved

Show column on matrix's lowest level only

Hi, I have a matrix visualiation that shows a hierarchy of products on the rows and some KPIs over time on the columns (each column is a month). On the rows, it's something like store->product type-...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi zivhimmel ,

     

    Create the following measure:

     

    Quantity filer = IF(HASONEFILTER(Sales[Product]);SUM(Sales[Quantity]);BLANK())

    Then just use this on your matrix, the image below you can see the result of the quantity column and the measure created above.

     

    You can also change the BLANK() to any other value you want.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi zivhimmel ,

 

Create the following measure:

 

Quantity filer = IF(HASONEFILTER(Sales[Product]);SUM(Sales[Quantity]);BLANK())

Then just use this on your matrix, the image below you can see the result of the quantity column and the measure created above.

 

You can also change the BLANK() to any other value you want.

 

Regards,

MFelix

  • zivhimmel's avatar
    zivhimmel
    Icon for Resolver I rankResolver I
    7 years ago

    Thanks MFelix 

    This is a nice option. But what I'm really after is not showing the column at all when on the higher levels.

    I don't believe this is doable, might use your suggestion as it's better than a blind aggregation.

     

    • Grambi's avatar
      Grambi
      Frequent Visitor
      7 years ago

      I think it's doable. I've combined two tips from other users.

       

      This measure will only display the quantity if the Product Level is visible.

       

      Quantity = if(ISINSCOPE(Example[Product]);sum(Example[Quantities]);blank())

      I've added a KPI for the level Product Types, which has no use on the Level of Stores or Products.

      MyKPI_Types = IF(ISINSCOPE(Example[Product Type]);if(ISINSCOPE(Example[Product]);Blank();SUM(Example[MyKPI_all]));BLANK())

      Source: jdbuchanan71 https://community.powerbi.com/t5/Desktop/Drill-down-inherits-values-looking-for-help/m-p/811358#M390093

       

      And with this part Power BI will only show columns if there's data.

      You need to create a New Table first, and put all the names of the columns and measures with values that you want to display into a column.

       

      Measure = SWITCH ( SELECTEDVALUE ( NewTable[NewColumn] ); "KPI All"; sum(Example[MyKPI_all]); "KPI Prod Types"; [MyKPI_Types] ; "Quantity" ; [Quantity] )

      Source: https://community.powerbi.com/t5/Desktop/How-to-hide-null-columns-in-matrix-table/td-p/561564

      v-chuncz-msft

       

      Below you'll see the same matrix visual three times,  displayed on different levels.

      • Top Level only shows the main KPI.
      • The level Product Types (colors) show both the main KPI and the specific KPI for Product Types.
      • The lowest level shows the main KPI and the quantity for all the products (A,B,C,D,etc).