Data-modelling of unragged/unbalanced hierarchies with pre-aggregated values

[Anonymous]

Hello everyone,

my data contains an unragged/unbalanced hierarchy. The hierarchy assigns different stores to their spatial affiliation. Additionally, each store has a list of orders with a short description (e.g. RTX 3070). The mathematical relationships in this hierarchy are sometimes very complex. Therefore, I do not want Power BI to aggregate the values automatically. Instead, my data contains the correct values for each node. Accordingly, my (simplified) table looks like this:

H_ID	Level1	Level2	Level3	Level4	Level5	Value	Lvl
1	Europe					0	1
2	Europe	Middle				1	2
3	Europe	Middle	Germany			1	3
4	Europe	Middle	Germany	Store1		2	4
5	Europe	Middle	Germany	Store2		3	4
6	Europe	Middle	Germany	Store2	RTX 3070	5	5
7	Europe	Middle	Germany	Store2	RTX 3080	8	5
8	Asia					13	1
9	Asia	China				21	2
10	Asia	China	Store3			34	3
11	Asia	China	Store3	RTX 3090		55	4
12	Asia	China	Store3	RTX 2080		89	4

I have already found a solution how to display the hierarchy with the correct values in a matrix visual applying two DAX-measure:

Sum of Values = SUM('hierarchy_with_orders (old)'[Value])

Display Values = var level1 = CALCULATE([Sum of Values], 'hierarchy_with_orders (old)'[Lvl]=1)

var level2 = CALCULATE([Sum of Values], 'hierarchy_with_orders (old)'[Lvl]=2)

var level3 = CALCULATE([Sum of Values], 'hierarchy_with_orders (old)'[Lvl]=3)

var level4 = CALCULATE([Sum of Values], 'hierarchy_with_orders (old)'[Lvl]=4)

var level5 = CALCULATE([Sum of Values], 'hierarchy_with_orders (old)'[Lvl]=5)

var level1selected = ISINSCOPE('hierarchy_with_orders (old)'[Level1])

var level2selected = ISINSCOPE('hierarchy_with_orders (old)'[Level2])

var level3selected = ISINSCOPE('hierarchy_with_orders (old)'[Level3])

var level4selected = ISINSCOPE('hierarchy_with_orders (old)'[Level4])

var level5selected = ISINSCOPE('hierarchy_with_orders (old)'[Level5])

return SWITCH( TRUE(),

level5selected, level5,

level4selected, level4,

level3selected, level3,

level2selected, level2,

level1selected, level1,

"")

This leads me to the following correct result:

The entries RTX 3070/3080/3090/2080 represent some orders(-descriptions). In my actual data several 1.000 orders can appear per store. Also, there are many levels(~20). So, this table becomes really big. In order not to have to save the complete hierarchy for each order (produces a lot of overhead) I have thought about a more efficient data scheme with three tables:

1. A hierarchy-table which stores the hierarchy till the orders (maybe I dont need these "Orders"-rows with Hierarchy-ID 6 and 10 at all, but I am not sure):

Hierarchy_ID	Level1	Level2	Level3	Level4	Level5
1	Europe
2	Europe	Middle
3	Europe	Middle	Germany
4	Europe	Middle	Germany	Store1
5	Europe	Middle	Germany	Store2
6	Europe	Middle	Germany	Store2	Orders
7	Asia
8	Asia	China
9	Asia	China	Store3
10	Asia	China	Store3	Orders

2. A order-table which contains my orders. The Order-ID is unique across all stores. The Order Description can also occur frequently within a store:

Order_ID	Order Description
1	RTX 3070
2	RTX 3080
3	RTX 3090
4	RTX 2080

3. A fact-table which contains a mapping:

Value	Hierarchy_ID	Order_ID	Level
0	1	0	1
1	2	0	2
1	3	0	3
2	4	0	4
3	5	0	4
5	6	1	5
8	6	2	5
13	7	0	1
21	8	0	2
34	9	0	3
55	10	3	4
89	10	4	4

If I apply the previous  shown Measures: I get the following wrong result. Where all my orders are shown aggregated:

My question now is, how do I adjust my data model or DAX formula to get the correct result from the first screenshot.

Here is a link to my sample Power Bi File: https://www.dropbox.com/s/5vnm5ej29nnly2r/sample.pbix?dl=0

Thanks in advance.

framas

[AllisonKennedy · Accepted solution]

@Anonymous  Yes, it's not as straightforward as just adding order to 'rows' since as you said the levels are not always the same and there is nothing in your measure or data telling Power BI how to connect the order to the heirarchy. 

You've stumped me on this one, without knowing the complex formulae that create this complex hierarchy, I can't think of a way to simplify the data model. I'm also not sure the three table method would be any simpler given that each order number is unique across all the stores. In a standard sample data model you would have one table for all your stores and one for all your orders, and roll up/ SUM the hierarchy for country, region, etc but you have said this won't work in your dataset? 

[Anonymous]

@AllisonKennedy Only at the lowest level of the orders would automatic aggregation be allowed (the sum of the orders gives the value for the store). But above that level, the system should not aggregate automatically.

I could perhaps remove the aggregated values for the stores from the data set and add an additional column "IS_Leaf" (Boolean). Then I could check the value of the field. If the value is TRUE aggregate, otherwise use my previous formula expression (DisplayOfValues).

Unfortunately I don't know if and how to translate this into a DAX-formula or if it is even possible.

[v-shex-msft]

HI @Anonymous,

Have you saw the following blog about parent-child hierarchy handle and transform them into the general hierarchy structure? It probably helps with your scenario.

Parent-child hierarchies 
After you rebuild your hierarchy structure, the default matrix aggregate functions may work with them.

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@Anonymous  You need to somehow add the 'leaf' to the rows of the matrix, so this would be 'leaf description' I'm guessing in your sample scenario? in order to be able to do this, it needs to be a snowflake schema, so you must have hierarchy connected to leaf connected to fact. Your sample data is too abstract for me to connect the dots and provide further examples - are the leaf entries the same for all hierarchies, or different for each one? 

[Anonymous]

@AllisonKennedy 

The leaves in my hierarchy are the orders of a store. The orders of a store can vary from store to store, so they are not identical for all stores. I have adapted the post and the powerbi-file accordingly, to make it more clear.

I have already tried to add the order to the "rows" of the matrix visual. But that did not work. Another problem is that the hierarchy is unbalanced. For example, orders can appear on level 5 as well as on level 4 of the hierarchy. This has the consequence that I got nodes displayed without text.

I hope this helps you and makes my example a bit more understandable.

Thanks for the help
frames