Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Hi @Mani1205
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 @Mani1205
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.
Great appreciated .... is it possible to fill the blanks as zero ?
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
Hi @v-juanli-msft
is it possible to change name of total to Differnce along with that one more difference column WK5 - ( Avg of past weeks) ? Appreciate your help . Thanks
Hi Team,
Is the above requirement is not possible ?
Thank you.. I have tried adding another difference as mentioned above picture but i am not able to do ..
is it possible to add one more difference last week minus avg of past weeks along with last week difference
Hi @Mani1205
I'm afraid it is impossible.
You could add another table beside the matrix.
Best Regards
Maggie
yes, that is another solution but when user sorts the data it creates a confusion
@Mani1205 , 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-La...
is there any other workarround to acheive this
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |