Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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.
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] )
Below you'll see the same matrix visual three times, displayed on different levels.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |