Adding a calculated variance - check my data!! Help!!

[tarrantmark]

Hi I have seen a few people posting with issues and most have been resolved with a fairly simple formula. Unfortunately that doesnt seem to work with mine. Ok so I have simplified my data table for this message which is essentially a list of customer orders

Order	Customer	product	Financial Year	Value
1	a	trouser	2021	500
2	b	trouser	2022	250
3	c	shirt	2021	20
4	d	jacket	2021	50
5	a	shirt	2022	100
6	b	jacket	2022	150

Using power BI I have been able to do a Matrix table visualisation and that would give me: -

Product	2021	2022
Trouser	500	250
Shirt	20	100
Jacket	50	150

What I cant seem to workout is how to then do a variance column to show the difference between 2022 and 2021. I assume its because the original table is just a list and not a summarised version (like the Matrix table). I cant seem to insert a column based soley on the Matrix table as it keeps referring to the original data table. 

Any clues please!!  I have loads of other things I want to do on Power BI but they probably ALL need a variance of some kind in there!! 

Cheers

Mark

[v-zhangti · Accepted solution]

Hi, @tarrantmark 

You can try the following methods.

Maesure:

Difference =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        [Financial Year] = 2022
            && [product] = SELECTEDVALUE ( 'Table'[product] )
    )
)
    - CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Financial Year] = 2021
                && [product] = SELECTEDVALUE ( 'Table'[product] )
        )
    )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[tarrantmark]

Hi - I tried to apply that to my main data and I am still getting errors - see screenshot. I obviously had to change a few bits to apply it to the different names/fields on my actual data but it just doesnt like it. I have also copied the error code : - 

Error Message:
MdxScript(Model) (5, 102) Calculation error in measure 'Data2'[Difference]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

If you have any further advice that would be gratefully recieved!! 

[tarrantmark]

Just to make it easier this is what I changed the formula to : - 

Difference = CALCULATE ( SUM ( 'Data2'[Actual Line Value] ), FILTER ( ALL ( 'Data2' ), [Financial Year] = 2022 && [Product Group] = SELECTEDVALUE ( 'Data2'[Product group] ) ) ) - CALCULATE ( SUM ( 'Data2'[Actual Line Value] ), FILTER ( ALL ( 'Data2' ), [Financial Year] = 2021 && [Product group] = SELECTEDVALUE ( 'Data2'[Product group] ) ) )