How to sum closingbalance and openingbalance measures for each row by category?

[vkong6m]

pbix and xls here: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

I have my measure [Investment Value] which calculates the value of my investments.

In this dummy data set I have 3 investments (see below) with their corresponding data:

However when I filter by the region of the investments USA shows blank (even though Happy investment should be under USA). 

The problem is under USA there is no data under the current calculation.

I could specifically reference each investment like this:

Happy and Sad = 
CALCULATE (
    [Investment Value],
    FILTER ( ALL ( 'Investment Data' ), [Investment] = "Happy Investment" )
)
    + CALCULATE (
        [Investment Value],
        FILTER ( ALL ( 'Investment Data' ), [Investment] = "Sad Investment" )

but given the actual data set has hundreds of investments I need something that's automatic:

Can someone please help me modify my 'Investment Data'[Investment Value] measure or help me write a new one for categories?

Thank you

[v-jingzhang]

Hi @vkong6m 

In your current "Investment Data" table, there is no Region column. While in "Investment Region" table, there are many duplicated Region-Investment pairs. It's not able to tell which investment row in "Investment Data" table is related to which region in "Investment Region" table. The current relationship is not clear so the filtering fails. 

Best Regards,
Community Support Team _ Jing