Forum Discussion
row value to column value and find difference
Hi Team,
I have a input data like below
Col1 Col2 Col3
A Wk 1 10
B WK 2 5
C WK 3 20
B WK 4 10
A WK 5 5
D WK 5 20
I want the output like below
Col1 wk1 wk2 wk3 wk4 wk5 dif_wk5-wk4
A 10 0 0 0 5 5
B 0 5 0 10 0 -10
C 0 0 20 0 0 0
D 0 0 0 0 20 20
I have used matrix to show and adding diff shows diff_wk5-Wk4 accorss all wk
pls help
Hi Anonymous
Create a measure
Measure = IF ( ISINSCOPE ( 'Table 3'[ Col2 ] ), SUM ( 'Table 3'[ Col3] ), CALCULATE ( SUM ( 'Table 3'[ Col3] ), FILTER ( 'Table 3', 'Table 3'[ Col2 ] = "WK5" ) ) - CALCULATE ( SUM ( 'Table 3'[ Col3] ), FILTER ( 'Table 3', 'Table 3'[ Col2 ] = "WK4" ) ) )
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.
11 Replies
Anonymous , if you looking for Week on Column and Diff only on Grand total That is not possible. I logged a idea for that, vote for that.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table
Please check for WOW - https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Hi Anonymous
Create a measure
Measure = IF ( ISINSCOPE ( 'Table 3'[ Col2 ] ), SUM ( 'Table 3'[ Col3] ), CALCULATE ( SUM ( 'Table 3'[ Col3] ), FILTER ( 'Table 3', 'Table 3'[ Col2 ] = "WK5" ) ) - CALCULATE ( SUM ( 'Table 3'[ Col3] ), FILTER ( 'Table 3', 'Table 3'[ Col2 ] = "WK4" ) ) )
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.Hi
Since there is no row for the blank cell in your original data, it can't add 0 in current data model.
except we create another data model.
Best Regards
Maggie
