Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I am trying to calculate the percentage of volume shipped direct from our production sites.
The 3 numbers we need are:
External from site
Internal from site
Returned to site
(External from site - returned to site) / (External from site + internal from site)
We want this from manufacturing sites only - the challenge is that External & internal from the site uses the "From Plant" but the returned to uses the "To" field.
So basically I want to use the Plant From and map the the To field to that
The data looks like this:
Solved! Go to Solution.
Hi @SJ ,
Thanks for johnt75 reply.
You can try the following steps:
1.Create a calculatetable
ResultTable =
SUMMARIZE(
FILTER(
'Fact Table',
'Fact Table'[To] IN VALUES('Fact Table'[From plant])
),
'Fact Table'[To],
"Return to Plant",SUM('Fact Table'[Vol])/DISTINCTCOUNT('Fact Table'[Material])
)
2.Create one to one relationship between Plant Table and Result table
2.Create a calculate column in Fact table
Return to Plant = RELATED(ResultTable[Return to Plant])
3.Create measures
External =
CALCULATE(
SUM('Fact Table'[Vol]),
FILTER(
'Fact Table',
'Fact Table'[Delivery Type] = "External" && RELATED('Plant Table'[Type]) = "Manufacturing Site"
)
)
Internal =
CALCULATE(
SUM('Fact Table'[Vol]),
FILTER(
'Fact Table',
'Fact Table'[Delivery Type] = "Internal" && RELATED('Plant Table'[Type]) = "Manufacturing Site"
)
)
Direct % = DIVIDE(([External]-SELECTEDVALUE('Fact Table'[Return to Plant])), ([External] + [Internal]),0)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @SJ ,
Thanks for johnt75 reply.
You can try the following steps:
1.Create a calculatetable
ResultTable =
SUMMARIZE(
FILTER(
'Fact Table',
'Fact Table'[To] IN VALUES('Fact Table'[From plant])
),
'Fact Table'[To],
"Return to Plant",SUM('Fact Table'[Vol])/DISTINCTCOUNT('Fact Table'[Material])
)
2.Create one to one relationship between Plant Table and Result table
2.Create a calculate column in Fact table
Return to Plant = RELATED(ResultTable[Return to Plant])
3.Create measures
External =
CALCULATE(
SUM('Fact Table'[Vol]),
FILTER(
'Fact Table',
'Fact Table'[Delivery Type] = "External" && RELATED('Plant Table'[Type]) = "Manufacturing Site"
)
)
Internal =
CALCULATE(
SUM('Fact Table'[Vol]),
FILTER(
'Fact Table',
'Fact Table'[Delivery Type] = "Internal" && RELATED('Plant Table'[Type]) = "Manufacturing Site"
)
)
Direct % = DIVIDE(([External]-SELECTEDVALUE('Fact Table'[Return to Plant])), ([External] + [Internal]),0)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
You could create a measure like
% Shipped =
VAR VisiblePlants =
VALUES ( 'Plant'[Plant] )
VAR Internal =
CALCULATE ( SUM ( 'Delivery'[Vol] ), 'Delivery'[Delivery Type] = "Internal" )
VAR External =
CALCULATE ( SUM ( 'Delivery'[Vol] ), 'Delivery'[Delivery Type] = "External" )
VAR Returns =
CALCULATE (
SUM ( 'Delivery'[Vol] ),
REMOVEFILTERS ( 'Plant' ),
TREATAS ( VisiblePlants, 'Delivery'[To] )
)
VAR Result =
DIVIDE ( External - Returns, External + Internal )
RETURN
Result
This assumes that there is a relationship from the Plant table to the Delivery table using the Delivery[From] column, and that you are using the Plant[From] column in the visuals.
hi @SJ
To make it easier for anyone to help you, please post a workable sample data (not an image), your expected result from the same sample data and the reasoning behind. You may post a link to an Excel file stored in the cloud.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |