Changing matrix visual with hierarchy slicer

[Anonymous]

Hi all,

I am new to Power BI and would really appreciate some help with what I'm trying to achieve. Have tried to figure something out, but unable to find a solution yet. 

I have a data set (sample) consisting of the following hierarchy: 

In a matrix, if I select any one (or more) of the categories at the lowest level using the slicer (i.e Ferrari, Honda,...,Black,...,Orange, Pineapple etc) I want there to be an individual column for each of the lowest level categories selected in the matrix, as seen below:

Therefore if my selections are as follows, then I want the matrix to display the columns as seen below (with the values in the matrix being the sales for each column category at each individual shop)

If however, I make selections for the upper level categories (i.e Cars, Colours, Fruits..), I want the following matrix display, where Cars includes the sum of Ferrari, Honda, Mitsubishi and Toyota under a single column called "Cars" and similarly for Fruits. 

Similarly, if Cars, Colour and Fruits are all selected - I want a single column (titled "Sample") displaying the sum of Cars, Colour and Fruits. 

Thank you!!

[v-zhenbw-msft]

Hi @Anonymous ,

Sorry for that cannot use Slicer to control hierarchy display.

Maybe you can create two matrix tables, one has the first level and another has second level.

The result like this,

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Many thanks for your responses - I've figured it's best to use matrix drill down to achieve what I'm trying to do, was probably being a bit overly ambitious trying to make it work with slicers 🙂 

[v-zhenbw-msft]

Hi @Anonymous ,

Maybe you can submit the requirement to ideas and add your comments there to make this feature coming sooner 😊: https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , that will not happen by visual. With help from allselected and all, you have count member and if they are same then you have to change the column. For that, you might need bookmarks

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

[Anonymous]

@amitchandak thank you for your reply, much appreciated. 

Please can you elaborate what you mean by "@PBlearner27 , that will not happen by visual. With help from allselected and all, you have count member and if they are same then you have to change the column. For that, you might need bookmarks"?

Do you mean it's not possible to have the matrix columns change as I had described without the use of bookmarks??