Multi Parent Hierarchy - Summations from Parent to Parent

[haiglerk2]

Hi All,

I have a table with multiple parents. And I need to count that table in two different ways, and then return the sum of one of those counts.

I work in Container shipping, but I'll use an example of standard box shipping for reference.

One Truck can have many customers shipments on the truck.

One Parent Customer can have multiple subsidiaries.

I need to be able to return, the total number of shipments for each parent company, as well as the total number of shipments on the truck that the parent customer had a shipment on.

Some sample data below:

Truck A had 500 shipments: 100 for company W. 150 for company X. 200 for company Y. 50 for Company Z.

Truck B had 250 shipments: 75 for company W. 25 for company X. 50 for company Y. 100 for company Z.

***Truck C had 200 shipments: 200 for company X

Company W and Y are owned by Parent Comany L.

Company X and Z do not have a parent company.

I need to be able to return:

Company	Num Shipments	Total Shipments
L	425	750
X	375	950
Z	150	750

Any suggestions on how I can do this?

***Data update from original post that is relevant to get to the meat of the question.

[parry2k · Accepted solution]

@haiglerk2 try this measure, I think this is what you are looking for.

Total Containers = 
VAR __t = CALCULATETABLE ( VALUES ( Containers[Truck] ), ALLSELECTED ( Alias[Alias] ) ) 
RETURN 
CALCULATE ( [Sum Containers], REMOVEFILTERS ( Alias[Customer] ), __t )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[haiglerk2]

I'm following pretty good what's happening in this formula, but I'm not quite sure what purpose the REMOVEFILTERS has here. 

I have a couple other use cases that I believe I can adapt this for, but need to understand what's happening.

[haiglerk2]

That did it! 
Thank you!

[parry2k]

@haiglerk2 what is the logic to other gets 750 (I mean L and Z) Why it is 750 for those?

[haiglerk2]

Parent companies L and Z have shipments on trucks A and B. A had a total of 500 shipments, B - 250. 

So total number shipments on the trucks carrying shipments for company L, is 750.

[parry2k]

@haiglerk2 not sure if I understood the logic when you said it should be 750 for L and Z? What about X? 

[haiglerk2]

X would now include Truck C, so there would be 375 shipments that are for company X. 950 shipments on trucks that involved comany X.

I updated the OP with Truck C to give a fuller picture of the data as well.

[parry2k]

@haiglerk2 look at the attached file, 3 tables you gave with two measures. There are other tables in the model, just ignore those.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[haiglerk2]

This is almost what I need
Lets say there is truck C, with 200 shipments, all for company X.

I would still need just 750 total shipments, for companies L and Z. But the new totals for X would be 375/950.

[CNENFRNL]

A simple word game, a company without parental company literally means its parental company is itself.

then a simplest measure does the trick,

[haiglerk2]

Each of those fields I can get seperately. I'm just unsure how to return them in the same visual, since the parent company is not related to the truck. Basically, from what you returned, I need to add another column or measure that is the total number of shipments on trucks that L had shipments.

[parry2k]

@haiglerk2 it is pretty easy but you need to show how exactly your data tables are and how they are connected. it is not about the calculation, it is about the model. One thing is, if x and z, don't have a parent, you have to prepare the data to make the company a parent company so that data is structure correctly. Once this is done, everything will be super easy.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[haiglerk2]

Hi,

This is a small segment of a much larger dataset. Below are the relationships.

The way my alias mapping table is set up, if it doesn't have a parent, I have it set to be it's own parent.

KH_Alias_Mapping is related by Alias to Line Operator on the COntainers  Table.

VesVoy is my key field and refernce field linking the Vessel Voyages and Contianers tables. 

Both are set to 1:Many.

To Keep with my first example, below would be how the tables are set up:

"Vessel Voyages"

Truck	Shipments
A	500
B	250

"Containers"

Company	Moves	Truck
Z	50	A
Z	100	B
Y	200	A
Y	50	B
X	150	A
X	25	B
W	100	A
W	75	B

"Alias Mapping"

Alias	Customer
W	L
Y	L
Z	Z
X	X