cancel
Showing results for
Search instead for
Did you mean:
RDLOPEZ Frequent Visitor

## Negative to positive gains showing as a negative variance in Matrix

Hi All,

I'm trying to finish up a detailed financial report but i'm running into some inconsistencies with the variance cells.
Seems to me that BI is calculating all the values as positives even when they are negative values.

Positive to positive changes work perfectly but once a negative value is dropped in it shows opposite variances.

As seen in the clip below -53,562.63 to +13,346.04 shouldn't be a negative percentage as that is a positive gain. Yet it shows it as a -125% loss. Same as -1,011.12 to -175,585.34 is a loss but yet shows a positive percentage gain of 17,265%

Here is the measure i'm using to calculate this:

Prior Month Variance =
IF(
ISFILTERED('Finance'[Posting Date.Month]),
VAR __PREV_MONTH =
CALCULATE(
SUM('Finance'[Current Total]),
DATEADD('Finance'[Posting Date.Month].[Date], -1, MONTH)
)
RETURN
DIVIDE(SUM('Finance'[Current Total]) - __PREV_MONTH, __PREV_MONTH)
) Thank You For Your Assistance,
Ray Lo the Data Bro

1 REPLY 1
natelpeterson Senior Member

## Re: Negative to positive gains showing as a negative variance in Matrix

@RDLOPEZ - When you divide a positive and negative number, it is negative. There needs to be a business rule as to how you want to handle these scenarios. Then you can incorporate the logic into your calculation.

Hope This Helps,

Nathan