Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vkong6m
Frequent Visitor

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

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:

vkong6m_0-1657209142657.png

 

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.

 

 

vkong6m_1-1657209202203.png

 

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

 

 

 

 

1 REPLY 1
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1657533614734.png

vjingzhang_1-1657533692240.png

 

Best Regards,
Community Support Team _ Jing

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.