Financial Reporting - show expenses as positive numbers


#1

How do I change the formulae below so that expenses can appear as positive but at the same time get the right net profit (loss) number at the bottom?

Actuals ('000) =
VAR Revenue = CALCULATE( SUM( ‘Financial Data’[Values] ), ‘Financial Data’[Actual/Budget] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Sub Category] = “Revenue” ) )
VAR Expenses = CALCULATE( SUM( ‘Financial Data’[Values] ),‘Financial Data’[Actual/Budget] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Sub Category] = “Expenses” ) ) * -1

RETURN
DIVIDE( Revenue + Expenses, 1000, 0 )


#2

I think this should be relatively straight forward.

Actuals ('000) =
VAR Revenue = CALCULATE( SUM( ‘Financial Data’[Values] ), ‘Financial Data’[Actual/Budget] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Sub Category] = “Revenue” ) )
VAR Expenses = CALCULATE( SUM( ‘Financial Data’[Values] ),‘Financial Data’[Actual/Budget] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Sub Category] = “Expenses” ) ) *** -1** - (get rid of this)

RETURN
DIVIDE( Revenue + Expenses, 1000, 0 ) (turn this into Revenue - Expenses)

Hopefully I’m understanding you right on this one


#3

I had tried that, and just did it again to be sure, but it still shows expenses as negative. Moving the " * - 1 " from the “CALCULATE” to “DIVIDE” functions didn’t change expenses sign.

Example: want results displayed as…
Revenue 100,000
Expenses 60,000
Profit 40,000

Not…
Revenue 100,000
Expenses - 60,000
Profit 40,000

Thanks for you help.


#4

Sorry my bad, you’re correct.

Interesting one, I actually confused myself trying to work this out, but understand why it works out that way.

It’s because the expenses are always negative if there is no revenue actually in the calculation. Ie. just summing up an expense line

I think this will get you what you need

Actuals ('000) = 
VAR Revenue = CALCULATE( SUM( 'Financial Data'[Values] ), 'Financial Data'[Actual/Budget] = "Actual",
                       FILTER( 'Financial Categories', 'Financial Categories'[Sub Category] = "Revenue" ) )
VAR Expenses = CALCULATE( SUM( 'Financial Data'[Values] ),'Financial Data'[Actual/Budget] = "Actual",
                       FILTER( 'Financial Categories', 'Financial Categories'[Sub Category] = "Expenses" ) )

RETURN
DIVIDE( Revenue -
    IF( ISFILTERED( 'Financial Categories'[Financial Statement List] ), Expenses * -1, Expenses ),
        1000, 0 )

#5

:+1:t4: Super! Worked. Thanks so much.