Calculation of total product qty shipped by location percentage

[John_freemont]

Hi,

Hoping someone can help.

I'm getting stumped on this one, where I'm trying to assign product quantity shipped to the quantity from location. i.e each product has a different percentage of it made from different locations and I need to be able to show the total quantity shipped by location produced.

I've included an exemplar PBIX (http://www.filedropper.com/calculationissueexample ) with synthetic data in it and is a small subset of the total model I've inherited, so changes to the model aren't really an option. The only difference my syntehtic data doesn't show is the 1 to 1 relationships you'll see are really 1 to many in the real model (Calendar -> Shipment Detail and Shipment -> Shipment Detail).

The outcome should look like this and also be able to be filtered by a Year context:

Location	Total Qty
A	6148.5
B	1892.5
C	6809

The calculation needs to happen at a row level between Product Content.Percentage_of_Product and Shipment Item.Qty. An exemplar theroteical table to create what I am after is below, it isn't a table that is in the actual model and I can't quite firgure how to bring it together for the calculation I need.

Product_ID	Product Content.Percentage_of_Product	Product Content.Location	Shipment Item.Qty	Product Content.Percentage_of_Product * Shipment Item.Qty
1	20	A	5300	1060
1	30	B	5300	1590
1	50	C	5300	2650
2	98	A	3200	3136
2	2	C	3200	64
3	100	C	3600	3600
4		A	1200	0
5	71	A	2750	1952.5
5	18	C	2750	495
5	11	B	2750	302.5

Thanks.

[John_freemont · Accepted solution]

Alright. Managed to figure it out.

Here's my resolution

Measure =
SUMX (
    SUMMARIZE (
        'Product Content',
        'Product Content'[Product_ID],
        'Product Content'[Percentage_of_Product],
        "calc", SUM ( 'Shipment Item'[Qty] )
            * FIRSTNONBLANK ( 'Product Content'[Percentage_of_Product], 1 ) / 100
    ),
    [calc]
)