Power BI - totals per related entity

0
votes

Basically, I’d like to get one entity totals, but calculated for another (but still related/associated!) entity. Relation type between these entities is many-to-many. Just to be less abstract, let’s take Trips and Shipments as mentioned entities and Shipments’ weight as a total to be calculated. Calculating weight totals just per each trip is pretty easy task. Here is a table of Shipments weights: shipments with their weights

We place them into some amounts of trucks/trips and get following weight totals per trip: Shipments' weight total per trips

But when I try to show SUM of Trip weight totals (figures from 2nd table) per each related Shipment (Column from 1st table), it becomes much harder than I expect. It should look like: needed table I can't get in Power BI

And I can’t get such table within Power BI.

Data model for your reference: data model

Seems like SUMMARIZE function is almost fit, but it doesn’t allow me to use a column from another table than initialized in the function: almost fit but not

Additional restrictions:

Selections made by user (clicks on cells etc.) should not affect calculation anyhow.

The figures should be able to be used in further calculations, using them as a basis.

Can anyone advise a solution? Or at least proper DAX references to consider? I thought I could find a quick answer in DAX reference guide on my own. However I failed to find a quick answer.

1
powerbirelational-databasedax

1 Answers

1
votes

Version 1

Try the following DAX function as a calculated column inside of your shipments table:

TripWeight =
VAR tripID =
    RELATED ( Trips[TripID] )
RETURN
    CALCULATE (
        SUM ( Shipments[ShipmentTaxWeightKG] );
        FILTER ( Shipments; RELATED ( InkTable[TripID] ) = tripID )
    )

The first expression var tripID is storing the TripID of the current row and the CALCULATE function gets the SUM of all of the weight for all the shipments that belong to the current trip.

Version 2

You can also create a calculated table using the following DAX and create a relationship between the newly created table and your Trips table and simply display the weight in this table:

TripWeight =
GROUPBY (
    Shipments;
    Trips[TripID];
    "Total Weight KG"; SUMX ( CURRENTGROUP (); Shipments[ShipmentTaxWeightKG] )
)

Version 3

Version 1 and 2 are only working if the relationship between lnkTrip and Shipment is a One-to-One relationship. If it is a many-to-one relationship, the following calculated column can be created inside of the Trips table:

ShipmentTaxWeightKG by Trip = SUMX(RELATEDTABLE(Shipments); Shipments[ShipmentTaxWeightKG])

hope this helps.