topic Re: Calculate Net Value with Measure in DAX Commands and Tips

Calculate Net Value with Measure

Otgoo — Tue, 26 Jul 2022 12:38:58 GMT

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

Re: Calculate Net Value with Measure

johnt75 — Tue, 26 Jul 2022 16:11:41 GMT

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]

Re: Calculate Net Value with Measure

Otgoo — Wed, 27 Jul 2022 00:46:32 GMT

Thank you so much
It is brilliant 😊