Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mani1205
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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" )
        )
)

Capture14.JPG
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.

View solution in original post

11 REPLIES 11
v-juanli-msft
Community Support
Community Support

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" )
        )
)

Capture14.JPG
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

 

Mani1205_0-1593077905439.png

 

Hi Team,

 

Is the above requirement is not possible ?

Hi @Mani1205 

In The format setting of the visual->Subtotal

Capture15.JPG

Best Regards

Maggie

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

amitchandak
Super User
Super User

@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

Hybrid.png

 

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.