How do I summarise against a site in one column that is held in a different column

[SJ]

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:

[Anonymous · Accepted 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

[johnt75]

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.

[danextian]

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.