Best Visual for Drilldown in Matrix, Table or Heat Chart

[Anonymous]

Hi 

New to Power BI and was wondering if someone could recommend me the visualization for the following problem:

Visualization – Matrix, Table, Heat Chart (example in the image)

X axis - Catagory 1 

Y axis - Catagory 2, with subsets

Values - Number of cases (which belong to both Catagory 1 and Catagory 2)

Feature – Conditional Formatting (shades of blue – high/low) and Drilldown using one table

Thanks

[amitchandak]

@Anonymous , Matrix seems to be a better option for you

These 4 should help you

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

[Anonymous]

Yup matrix seems like the best choice 🙂 thanks a lot for the solution. 

I had a question relating to the automatic sorting (higher number on the bottom left). Is there a way I can do this? I tried the ellipsis -> sort by. But this only rearranges rows but not columns (higher values on the bottom). How can I move these to values to bottom left? (prefer if I can avoid the rankx function) 

E.g. Automatically Move columns 3 and 6 to rows  1 and 2, even when I drill-down into subcategory/subsubcatagory. 

Sort each row - row 2 highest value is 285 but it is not at the bottom.

[v-jayw-msft]

Hi @Anonymous ,

As far as i know you should be able to sort the martix by column header but not by the coulumn value.

Best Regards,

Jay

[mihart]

Oh, wait, you already tried that. OK.  Do this instead...

Hover over the column you want to sort and a tiny arrow will appear.  Click the arrow to change the sort order. Sometimes you can simply click on the column header to change the sort too.

Ignore my previous reply 😉

Michele

[Anonymous]

Unfortunately, that does not work for columns (no little arrow appears at the edge of the cells). In the entire visualization, the little arrow only appears once - over only the rows subtotal field. It doesn't appear for the subtotal column field. It also does not appear for separate rows (clicking on row leads to drill down) or columns (stay the same, no arrow)

Cheers for the quick replies

Rudra

[mihart]

Hi @Anonymous 

To change the sorting 

1.  Select the visual to make it active
2. From the More options (...), select "Sort by" and make sure to select your Y axis (that's the one you want to change, right)
3. Then from More options(...), select Ascending or Descending.

HTH

Michele

[mihart · Accepted solution]

Hi @Anonymous 

Drilldown works on many different types of visuals.  You could use a matrix or a column chart or maybe a treemap. 

Why not try a bunch of different viz types and see which represents your data best?

- Michele