Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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]
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |