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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!