Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to create a measure which calculate the difference of 2 columns in a matrix and chance according to the slicer selections.
I have read some other topics, however it seems very specific for every case and I can't find a solution for mine.
My report page:
The data which forms the numbers looks as follows:
Customer | product group | product | date | Value |
10023 | 10 | 101540 | 6.7.2018 | 19,5 |
10023 | 10 | 101540 | 15.7.2018 | 58,5 |
10023 | 10 | 102560 | 6.7.2018 | 19,5 |
Now I would like to add 2 columns that calculate the difference of the numbers in the table.
As follows:
2017 | 2018 | Difference | Index |
10836797,24 | 12500320,78 | 1663523,54 | 115,4 |
779662,79 | 789100,60 | 9437,81 | 101,2 |
994985,29 | 1058116,88 | 63131,59 | 106,3 |
278138,70 | 325577,87 | 47439,17 | 117,1 |
6531291,03 | 6597573,42 | 66282,39 | 101,0 |
Difference = 2018 - 2017
Index = 2018 / 2017
If any questions please ask.
Hope someone can help me out.
Kind regards,
Guido
Solved! Go to Solution.
I saw you have a calendar table
Create these measures:
SumofValue = Sum(Table[Value])
SumOfValuePrevYear = Calculate([SumofValue];SAMEPERIODLASTYEAR(Calendar[date])
Difference = [SumofValue] - [SumOfValuePrevYear]
2018/2017 = [SumofValue] / [SumOfValuePrevYear]
Hope this help.
I saw you have a calendar table
Create these measures:
SumofValue = Sum(Table[Value])
SumOfValuePrevYear = Calculate([SumofValue];SAMEPERIODLASTYEAR(Calendar[date])
Difference = [SumofValue] - [SumOfValuePrevYear]
2018/2017 = [SumofValue] / [SumOfValuePrevYear]
Hope this help.
Hi @sokg
Thank you very much for your reply
It works, however I get the difference 2 times:
Do you have an idea how to fix this?
Kind regards,
Guido
Try select one year only (i.e. 2018)
Hi @sokg
Found a solution. Made 2 seperate measures in stead of using "Year" in Kalender table
Thanks again for your help!
Hi @sokg
That would be an option, however the desired format would be:
2017 | 2018 | Difference | Index |
10836797,24 | 12500320,78 | 1663523,54 | 115,4 |
779662,79 | 789100,60 | 9437,81 | 101,2 |
994985,29 | 1058116,88 | 63131,59 | 106,3 |
278138,70 | 325577,87 | 47439,17 | 117,1 |
6531291,03 | 6597573,42 | 66282,39 | 101,0 |
Any idea how we could create this?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |