Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |