Forum Discussion

Otgoo's avatar
Otgoo
Frequent Visitor
4 years ago
Solved

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 Am...
  • johnt75's avatar
    4 years ago

    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]