Creating a Hierarchy from Multiple Tables

[cjc322]

Hello,

I would like to create a Hierarchy for Drill Down purposes within a TABLE.

I currently have a report that has a START DATE and END DATE. When a user inputs two dates it shows the NETSALES and NETSALES COMPARISON w/ difference and percnetage difference. Currently I display this in a table by Department Number / Name. 

What I would like to do is display this by STORE number and be able to drill down.

I want to go:

STORE > DEPARTMENT > CLASS > SUBCLASS > ITEM SKU

All five of the above Fields are in different tables. Store, Department, Class, Item SKU are all also located in the FACT TABLE but SUBCLASS currently is not. However SUBCLASS has an active link to a table that does connect to the FACT TABLE with an active link.

Does anyone have any advice how to achieve this?

The result I want managers to go in. Select two dates to compare. See all sales for all stores and then drill down to speceifics within the same table to see where sales came from.

Thanks in advance.

Chris

[ankitpatira · Accepted solution]

@cjc322 in power bi desktop there is inline-hierarchy option but that only allows hierarchy from the current table. However if you have tables joined then you should be able to create drill down without any extra steps. All you need to do is select chart that supports drill down (column, bar etc) and put multiple fields from multiple tables (all joined) under Axis area.

[SIH007_1]

Thank you for the answer, works fine. If you create a hierarchy within one table you get such a nice grouping, where you see the hierarchy directly. Will that be possible with more tables sometime?

[Jsonify]

How can you use hierarchies across multiple tables on a shared dataset? Is that even possible?

[PowerDAX · Accepted solution]

You can also denormalize the fields into the table with the lowest granularity (assuming ITEM SKU).  This can be done on your SQL query (if using a query on a db) or iitem table (if other tables are all have relationships) by adding calculated columns to the item table....i.e.

SUBCLASS=RELATED( TABLE[SUBCLASS])

CLASS=RELATED( TABLE[CLASS])

DEPARTMENT=RELATED( TABLE[DEPARTMENT])

STORE=RELATED( TABLE[STORE])

[ankitpatira · Accepted solution]

@cjc322 in power bi desktop there is inline-hierarchy option but that only allows hierarchy from the current table. However if you have tables joined then you should be able to create drill down without any extra steps. All you need to do is select chart that supports drill down (column, bar etc) and put multiple fields from multiple tables (all joined) under Axis area.