How to have measure variance to flip the sign

[JustDavid]

As the subject says, I'm trying to create a measure that shows the variance between 2 measures - [measure_PrevAmount] and [measure_CurrAmount].

After googling, although was told that I could use SWITCH, my question now is that, given that my table could collapse or expand depending on user's view, I want to show the correct variance calculation.

As shown in the print screen, I have 6 cost codes, that made up of 'Revenue' category. But it's sub categories are 'Items Sold' (Obj_Code 1500, 1501 and 1502) and 'Promotions' (Obj_Code 1520, 1530 and 1540).

How can I flip the sign when it's Obj_Code 1500, 1501 and 1502 even though it's not showing in the table? In other words, those highlighted variance in yellow need to show positive amount, and that the Revenue subtotal will show 185 instead of -215

[danextian]

Hi @JustDavid 

Please try  this:

CALCULATE ( - [Variance], KEEPFILTERS ( 'table'[Sub Category] = "Items Sold" ) )
    + CALCULATE ( [Variance], KEEPFILTERS ( 'table'[Sub Category] <> "Items Sold" ) )

[Ritaf1983]

Hi @JustDavid 
You can use the measure :

Variance =
VAR itemsSoldVariance =
SUMX(
FILTER( 'Table', 'Table'[Sub category] = "items sold" ),
('Table'[prev amount] - 'Table'[curr amount]) * -1
)

VAR otherVariance =
SUMX(
FILTER( 'Table', 'Table'[Sub category] <> "items sold" ),
'Table'[prev amount] - 'Table'[curr amount]
)

VAR totalVariance =
SUMX(
'Table',
IF( 'Table'[Sub category] = "items sold",
('Table'[prev amount] - 'Table'[curr amount]) * -1,
('Table'[prev amount] - 'Table'[curr amount])
)
)

RETURN
IF( HASONEVALUE('Table'[Category]), itemsSoldVariance + otherVariance, totalVariance )

Result:

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[JustDavid]

@Ritaf1983 Instead of using Table[Sub category], if I want to incorporate your formula with [Obj_code], how do I take into consideration of 1500, 1501 and 1502?

[Ritaf1983]

Hi @JustDavid 
This is the same logic, different condition :

Variance =

VAR itemsSoldVariance =

    SUMX(

        FILTER( 'Table', 'Table'[obj code] IN {1500,1501,1502} ),

        ('Table'[prev amount] - 'Table'[curr amount]) * -1

    )

VAR otherVariance =

    SUMX(

        FILTER( 'Table', NOT 'Table'[obj code]  IN {1500,1501,1502}),

        'Table'[prev amount] - 'Table'[curr amount]

    )

VAR totalVariance =

    SUMX(

        'Table',

        IF( 'Table'[obj code] IN {1500,1501,1502},

            ('Table'[prev amount] - 'Table'[curr amount]) * -1,

            ('Table'[prev amount] - 'Table'[curr amount])

        )

    )

RETURN

IF( HASONEVALUE('Table'[Category ]), itemsSoldVariance + otherVariance, totalVariance )

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[v-yiruan-msft]

Hi @JustDavid ,

Based on the sample data you provided, could you clarify what your expected result is? To help us investigate further, would you kindly share additional details, specific examples, and a supporting screenshot illustrating the issue? Thank you for your assistance.

@Ritaf1983 Instead of using Table[Sub category], if I want to incorporate your formula with [Obj_code], how do I take into consideration of 1500, 1501 and 1502?

---

Best Regards

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Measure = if(Data[Obj_code]="1500"||Data[Obj_code]="1501"||Data[Obj_code]="1502",[measure_curramount]-[measure_prevamount],[measure_prevamount]-[measure_curramount])

Measure2 = SUMX(VALUES(Data[Cost code]),[Measure])

Hope this helps.

[JustDavid]

@Ashish_Mathur Quick question.

What's the purpose of Measure2 here?

[Ashish_Mathur]

To get the correct Grand Total.

[Ashish_Mathur]

Hi,

Try these measures

Measure = if(Data[Obj_code]="1500"||Data[Obj_code]="1501"||Data[Obj_code]="1502",[measure_curramount]-[measure_prevamount],[measure_prevamount]-[measure_curramount])

Measure2 = SUMX(VALUES(Data[Cost code]),[Measure])

Hope this helps.