row value to column value and find difference

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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.

[Anonymous]

Great appreciated .... is it possible to fill the blanks as zero ?

[v-juanli-msft]

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

[Anonymous]

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

[Anonymous]

Hi Team,

Is the above requirement is not possible ?

[v-juanli-msft]

Hi @Anonymous 

In The format setting of the visual->Subtotal

Best Regards

Maggie

[Anonymous]

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

[v-juanli-msft]

Hi @Anonymous 

I'm afraid it is impossible.

You could add another table beside the matrix.

Best Regards

Maggie

[Anonymous]

yes, that is another solution but when user sorts the data it creates a confusion

[amitchandak]

@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

[Anonymous]

is there any other workarround to acheive this