Forum Discussion
Matrix + percentage difference issue
I have monthly recurring tasks and I would like to calculate and visualize these figures. the base data is quite simple (see below), but I can't seem to get this right. I want to calculate the percentage difference between tasks and I would like power bi to show me the percentage difference between two months based on the filtered months that I select.
for example, I want to compare the month 202110 with the month 202210 and see the percentage difference of tasks. And when I change the filter values, I want to compare the months 202209 and 202210 in the same way.
i have huge problems with this. Can anyone help me?
| YYYYMM | Task | Value |
| 202110 | task 1 | 380 |
| 202110 | task 2 | 550 |
| 202110 | task 3 | 120 |
| 202209 | task 1 | 350 |
| 202209 | task 2 | 520 |
| 202209 | task 3 | 150 |
| 202210 | task 1 | 400 |
| 202210 | task 2 | 500 |
| 202210 | task 3 | 100 |
Hi AtomFiske ,
In this case you just need to change the second argument of the division use the following metric:
Variation = DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ), CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ) )If you want to present the values based on the YYYYMM also then you need to make some changes, this can be an option:
Variation = IF(ISINSCOPE('Table'[YYYYMM]), SUM('Table'[Value]), FORMAT(DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ), CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ) ), "#.00%"))
4 Replies
Hi AtomFiske ,
Try the following code:
Variation = DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ), CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) )Be aware that this code only works if you do not have the yyyymm on your table, if you need to have it this needs to be revised.
Hi! Thank you MFelix ! This is pretty close, but not quite what I was trying to describe. I probably didn't describe my problem well enough. I will try again with a picture.
I want to compare the task-specific percentage difference (see the red numbers on the picture) of the two months I selected with the filter, but I don't understand how to make this work.
Hi AtomFiske ,
In this case you just need to change the second argument of the division use the following metric:
Variation = DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ), CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ) )If you want to present the values based on the YYYYMM also then you need to make some changes, this can be an option:
Variation = IF(ISINSCOPE('Table'[YYYYMM]), SUM('Table'[Value]), FORMAT(DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MAX ( 'Table'[YYYYMM] ) ) - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ), CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[YYYYMM] = MIN ( 'Table'[YYYYMM] ) ) ), "#.00%"))
