cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

is there any other workarround to acheive this 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!