Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
SJ
Helper I
Helper I

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

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:

SJ_0-1736521558249.png

 

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1736737578876.png

 

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

vheqmsft_1-1736737661905.png

 

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

 



View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1736737578876.png

 

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

vheqmsft_1-1736737661905.png

 

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
Super User
Super User

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
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.