cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zivhimmel Member
Member

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-> product.

I want to add a quantity column, but I only want it to be visible when drilling down to the product level.

Quantity doesn't make sense on higher level (2 eggs + one ketchup bottle doesn't really sum up to 3).

Is that doable somehow?

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Show column on matrix's lowest level only

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.

 

quanity filter.png

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Super User
Super User

Re: Show column on matrix's lowest level only

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.

 

quanity filter.png

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Highlighted
zivhimmel Member
Member

Re: Show column on matrix's lowest level only

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 Frequent Visitor
Frequent Visitor

Re: Show column on matrix's lowest level only

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#M390...

 

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/56156...

@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).

Schermafbeelding 2019-10-08 om 03.16.32.png

 

 

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 154 members 1,710 guests
Please welcome our newest community members: