cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RDLOPEZ Frequent Visitor
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)
)


Capture3.JPG

Thank You For Your Assistance,
Ray Lo the Data Bro

1 REPLY 1
natelpeterson Senior Member
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