making calculations return a value of zero if part doesnt exist, prob w/meas not filtering properly

[CL7777]

I am an intermediate BI/Dax user and I have something that is stumping me. I choose a part number and this part number is related to grandparent part numbers by connecting the part table to the grandparent part table. These grandparent part numbers are connected/related to an invoice table which contains the revenue of the grandparent part numbers.

I would like to list in a table, the part number, the grandparent part numbers, and the revenue for the grandparent part numbers. When I add in the measure which calculates the revenue, it no longer filters for just the grandparent part numbers

For example, i have part number cbx 325 which has a list of grandparents in a table called BOM. when input into a table the part number and the grandparent part numers and I filter for the part number equal to cbx 325, I get the following:

This is correct, it lists the part number, along with all the correct grandparent numbers

When I add in a column for the revenue, using a measure that says 

List PriceSum = CALCULATE(SUM('InvoiceLines'[listPriceTotalUSD]), ALLEXCEPT('_Product Multi BOM', '_Product Multi BOM'[GrandParent])) + 0.

What I want to get is:

 but what I actually get is a full list of every grandparent of every part. the table no longer filters for the grandparents that are connected to part CBX 325. It ends up showing all grandparents regardless of the original part number chosen.  I cannot figure out why.

Attached is a link to the pbix. I am pretty sure the measure for listPriceSum needs to have a filter in there to allow it to show just the grandparents as shown above. Any help would be so much appreciated. here is a link to the pbix file.

https://stoneagetools-my.sharepoint.com/:u:/r/personal/cara-lyn_lappen_stoneagetools_com/Documents/C...

Here is a clip of the simple model and connections

[CL7777]

Try this dropbox link.. 

https://www.dropbox.com/s/tlygox25bqiltdw/test.pbix?dl=0

[CL7777]

that measure is not correct syntax, it throws an error as there is no filter in it. I will try to get the file to you in another way. In the meantime, can you correct the syntax of the suggestion you made?

[v-xinruzhu-msft]

Hi @CL7777 

The file you have privided cannot open, you can try the following measure

List PriceSum = CALCULATE(SUM('InvoiceLines'[listPriceTotalUSD]), '_Product Multi BOM'[GrandParent])) + 0).

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.