difference instead of total in matrix?

[Anonymous]

I have the following data structure:

year period value  type etc...

2018     1      6        rent

2019     1      5        rent

currently I use matrix  with the following setup rows:type, column year, values:value

currently the total for rent show up  as11. I would like to see the variance between 2019year and 2018

so -1 as  a value and if its possible a percentage% -16%.

is it possible? im a rookie in PowerBI. any idea how can I manage to see the variance instead of the total using matrix function? or what else should I use? 

[v-juanli-msft]

Hi @Anonymous 

Create columns

Column =
CALCULATE (
    SUM ( Table2[value] ),
    FILTER (
        ALLEXCEPT ( Table2, Table2[period], Table2[type] ),
        Table2[year]
            = EARLIER ( Table2[year] ) - 1
    )
)

Column 2 = IF([Column]<>BLANK(),[value]-[Column])/[Column]

Create a measure

Measure = IF(ISINSCOPE(Table2[year]),SUM(Table2[value]),FORMAT(SUM(Table2[Column 2]),"0.00%"))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

THank you for the quick help. I tried to implenet the formulas but I have an error:too many arguments were passed to the sum function

is there any way to distribute my pbix file? I tried to upload my pbix file herehttps://gofile.io/?c=dsv2Jw

thank you