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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Otgoo
Frequent Visitor

Calculate Net Value with Measure

Hello everyone,
I need to calculate the net value with a few measures. 
I can add more measures when a new Player comes. But it is not suitable.
how can I handle it?

Input

SendReceiveAmount
AB100
AC200
BA50
BC50
CA100
CB30

 

Calculation

SendReceive   
 ABCTotal Received
A 100200300
B50 50100
C10030 130
Total Sent150130250530


Output

 SendReceiveNet Value
A300150150
B100130-30
C130250-20
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create a dimension table from your fact table like

People = DISTINCT( UNION( 
ALLNOBLANKROW( VALUES('Table'[Send]),
ALLNOBLANKROW( VALUES('Table'[Receive])
))

then rename the column in the dimension table and create inactive one-to-many relationships from your new dimension table to both Send and Receive columns on your fact table. Always use the dimension table in your visuals.

You can then create 3 measures like

Total Sent = CALCULATE( SUM( 'Table'[Amount]), USERELATIONSHIP( 'People'[Person], 'Table'[Send]) )

Total Received = CALCULATE( SUM( 'Table'[Amount]), USERELATIONSHIP( 'People'[Person], 'Table'[Receive]) )

Net amount = [Total Sent] - [Total Received]

  

View solution in original post

2 REPLIES 2
Otgoo
Frequent Visitor

Thank you so much
It is brilliant 😊

johnt75
Super User
Super User

Create a dimension table from your fact table like

People = DISTINCT( UNION( 
ALLNOBLANKROW( VALUES('Table'[Send]),
ALLNOBLANKROW( VALUES('Table'[Receive])
))

then rename the column in the dimension table and create inactive one-to-many relationships from your new dimension table to both Send and Receive columns on your fact table. Always use the dimension table in your visuals.

You can then create 3 measures like

Total Sent = CALCULATE( SUM( 'Table'[Amount]), USERELATIONSHIP( 'People'[Person], 'Table'[Send]) )

Total Received = CALCULATE( SUM( 'Table'[Amount]), USERELATIONSHIP( 'People'[Person], 'Table'[Receive]) )

Net amount = [Total Sent] - [Total Received]

  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors