Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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]
)
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]
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
21 |