Calc of Share in Container by PO

[MT4T]

Hello,

I have below table:

TD: LOAD
CONTAINER#_OR_HAWB#,
"PO clean",
Material,
Price_TD

FROM [LIB*]

Below with sample values

CONTAINER#_OR_HAWB#	"PO clean"	Material	Price_TD
1	88A	Cabinet	20
2	89B	Module	25
3	88Z	Engine	22
4	89B	Glass	30
5	85U	Cabinet	45
6	88A	Module	10
1	88A	Module	10
1	88A	Engine	30
4	88A	Glass	30
2	89B	Glass	21
1	85U	Glass	40
1	89B	Engine	19
6	85U	Module	39

Goal is to filter One of "PO clean" on sheet and see few KPIs:

1 Sum of Price_TD of all Material asigned to this PO

2 Sum of Price_TD of all Material included in CONTAINER#_OR_HAWB# which has at leas one position of selected "PO clean"

For instance when I select in "PO clean" value '85U'  :

1 Sum of (45,40,39) // as we have 3 rows of this PO in 3 three containers (5,1,6)

2 Sum of (20,45,10,10,30,40,19,39) // as we have in total 8 rows asigned to containers (5,1,6) which contain  "PO clean" value '85U'

This calculation will be used to calculate Share of point1 in point2 and this share will be applied to Freight Cost of Containers so we can see how much we pay for transport for distinct PO.

Any Idea?

[MayilVahanan · Accepted solution]

Hi

Try like below

1. Sum(Price_TD)

2. Sum({<CONTAINER#_OR_HAWB#=p(CONTAINER#_OR_HAWB#), [PO clean]=>}Price_TD)

[vinieme12]

As below

1 Sum of (45,40,39) // as we have 3 rows of this PO in 3 three containers (5,1,6)

=Sum(Price_TD)

2 Sum of (20,45,10,10,30,40,19,39) // as we have in total 8 rows asigned to containers (5,1,6) which contain  "PO clean" value '85U'

=sum( Aggr (NODISTINCT sum({<POclean=>} Price_TD),[CONTAINER#_OR_HAWB#]))

[MT4T]

Thank, you.

Your solution works somehow better.

It returns exact the same value as I calculated manually.