Forum Discussion
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
| 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 |
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]
2 Replies
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 😊
