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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors