cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Oleg_D
New Member

one entity's totals per another (but still related/associated!) entity

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:

 

We place them into some trucks/trips and get following weight totals per trip:

pic2.png

 

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:

pic3.png

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

 

Data model for your reference:

pic4.png

 

 

 

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:

pic5.png

 

Additional restrictions:

Selections should not affect calculation anyhow.

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

 

Can someone 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 but failed.

1 ACCEPTED SOLUTION

Accepted Solutions
Oleg_D
New Member

Re: one entity's totals per another (but still related/associated!) entity

Hi @v-joesh-msft ,

Basically I already found another, much more suitable solution for my case - just to add new calculated column to Trips table with a simple expression:

SUMX(RELATEDTABLE(Shipments); Shipments[ShipmentTaxWeightKG])

This solution allows me to keep many-to-many relationship between shipments and trips as well as to follow mentioned restrictions. It is important to keep it.

 

Solution found with Stackoverflow community help.

Here is a link to the same topic there:

https://stackoverflow.com/questions/59324512/power-bi-totals-per-related-entity

You may also find attached example there.

View solution in original post

2 REPLIES 2
v-joesh-msft Solution Sage
Solution Sage

Re: one entity's totals per another (but still related/associated!) entity

Hi @Oleg_D ,

I think your model structure needs to be adjusted. The table "Trips" and "Shipments" should be a one-to-many relationship to achieve the results you want, then you just need to create a calculated table similar to the following:

addc = 
ADDCOLUMNS (
    Shipments,
    "a",
    VAR a =
        RELATED ( Trips[Tripid] )
    VAR k =
        ADDCOLUMNS ( Shipments, "a", RELATED ( Trips[Tripid] ) )
    RETURN
        CALCULATE ( SUM ( Shipments[ShipmentTaxWeightKG] ), FILTER ( k, [a] = a ) ) 
)

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX5sYyIg_LBIoRcMXB...

If not your case, kindly share your sample data if you don't have any Confidential Information.

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Oleg_D
New Member

Re: one entity's totals per another (but still related/associated!) entity

Hi @v-joesh-msft ,

Basically I already found another, much more suitable solution for my case - just to add new calculated column to Trips table with a simple expression:

SUMX(RELATEDTABLE(Shipments); Shipments[ShipmentTaxWeightKG])

This solution allows me to keep many-to-many relationship between shipments and trips as well as to follow mentioned restrictions. It is important to keep it.

 

Solution found with Stackoverflow community help.

Here is a link to the same topic there:

https://stackoverflow.com/questions/59324512/power-bi-totals-per-related-entity

You may also find attached example there.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors