cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
haiglerk2
New Member

Multi Parent Hierarchy - Summations from Parent to Parent

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:

CompanyNum ShipmentsTotal Shipments
L425750
X375950
Z150750

 

Any suggestions on how I can do this?

 

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

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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.

That did it! 
Thank you!

parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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
Super User III
Super User III

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

Screenshot 2021-06-24 204252.png

then a simplest measure does the trick,

Screenshot 2021-06-24 204747.png

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
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi,

 

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

 

haiglerk2_0-1624559797089.png

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"

TruckShipments
A500
B250

 

"Containers"

CompanyMovesTruck
Z50A
Z100B
Y200A
Y50B
X150A
X25B
W100A
W75B

 

"Alias Mapping"

AliasCustomer
WL
YL
ZZ
XX

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors