Flip number sign based on filter context

[GertWieland]

Hi community,

I'm trying flip the plus/minus sign when displaying numbers (i. e. measures) depending on filter context.
This is fairly common for financial reporting, and there are likely Power BI frontend solutions (for purchase). However, I need to solve the issue via DAX because we use an SSAS data model, and I need to make sure that users that connect via Excel get the same results as users who consume Power BI reports.

Here's a simple example of our situation:
We have a table called "Transactions" with two relevant columns: TransactionType, Amount.
TransactionTypes can be "Revenue" or "Expense", and for both, the numbers can be positive or negative, i. e. there are positive and negative revenues, and there are positive and negative expenses.
The measure "Total" simply needs to add up the individual numbers with their actual sign. However, when showing Expenses and Revenues separately, we need to show expenses with their "plain English" value.
In the example below, the amount "-120" is a normal expense. But if somebody asks "how much did it cost", the answer is "120", not "-120".
The Expense "100" is like a "reversal", i. e. a transaction that reduces the Expenses by 100.

TransactionType	Amount
Expense	-120
Expense	100
Revenue	200
Revenue	-50
TOTAL	= -120 + 100 + 200 - 50 = 130

However, when showing totals by TransactionType, we need to flip the sign when displaying the number.

Total Expense	Total Revenue	Total
20 (not data value -120 + 100 =-20, because Expense value is 20)	150	-20 + 150 = 130

We can't ALWAYS show Total Expenses as positive. If the numbers in the example were "120" and "-100", the sum would be "20". However, since this would be an (unusual) decrease of expenses, we'd need to show it as "-20", so users can distinguish it from the "normal" 20.

I believe that the solution involves the "FORMAT" command in Dax. However, I'm not sure how to build it so it responds to the filter context. In simple terms, when showing TransactionType "Expense", it should flip the sign for the display (but not when displaying the overall total).

I'd really appreciate some help here, thanks a lot in advance.

[DimaMD · Accepted solution]

@GertWieland  hi, try it 

Measure = ABS( SUM('table'[Amount]) )

Measure 2 = 
CALCULATE( 
    SUM( 'table'[Amount] ),
        ALLSELECTED('table'[TransactionType])
    )