Sum based on a value coming from another table

[85dhruv]

Hello,

I am very new to DAX and PowerBI in general so incase this is already answered then please let me know.

So i have 2 tables

Main Opportunity Table
Oppoutunity Id	Sale (EURm)
111	10
222	15
333	20
444	25
555	30

Upsell Table
Main opportunity	Upsell Opportunity
111	555
111	444
222	333

Now i would like to see

Main Opp	Up Sell Sales (EUR m)
111	55	(sum of 555 and 444 from main table)
222	20	(sum of 333 from main table)

I have tried to read upon parent-child and use Path but that required the child (Upsell id) to have all the parent ids (Main opp id) - which i dont have 

Can someone please guide me how to get to this ?

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[85dhruv]

Thanks @v-diye-msft @Ashish_Mathur , both accepted solutions worked . i used the one without the Merge since the table size i am handling is huge and it was taking a lot of time to create another table.

[Ashish_Mathur]

You are welcome.

[v-diye-msft · Accepted solution]

Hi @85dhruv ,

Set the relationship as below:

then add the measure:

Measure = IF(MAX('Upsell Table'[Main opportunity])=BLANK(),BLANK(),CALCULATE(SUM('Main Opportunity Table'[Sale (EURm)]),ALL('Upsell Table'),VALUES('Upsell Table'[Main opportunity])))

Result shown as below:

Pbix attached: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ET7KnBDDuyFEqZAAZsrRDR...

Best regards,

Dina Ye

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mussaenda]

Hi,

I am not sure if this is what you are looking for

I set the relationship like this:

Active: Opportunity ID -> Main Opportunity

Inactive: Opportunity Id <-> Upsell Opportunity

Then I created a measure (See above)

[edmar_502]

Hi @85dhruv 

Have your heard about the bridge table, perhaps it could be help you.

Additionally, study SUMX Dax function: https://docs.microsoft.com/en-us/dax/sumx-function-dax

https://www.seerinteractive.com/blog/join-many-many-power-bi/