Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Send | Receive | Amount |
A | B | 100 |
A | C | 200 |
B | A | 50 |
B | C | 50 |
C | A | 100 |
C | B | 30 |
Calculation
Send | Receive | |||
A | B | C | Total Received | |
A | 100 | 200 | 300 | |
B | 50 | 50 | 100 | |
C | 100 | 30 | 130 | |
Total Sent | 150 | 130 | 250 | 530 |
Output
Send | Receive | Net Value | |
A | 300 | 150 | 150 |
B | 100 | 130 | -30 |
C | 130 | 250 | -20 |
Solved! Go to Solution.
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]
Thank you so much
It is brilliant 😊
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]