Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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:
And I can’t get such a table within Power BI.
Data model for your reference:
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:
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.
Solved! Go to Solution.
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:
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.
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:
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.
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:
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.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |