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

Get 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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

is there any other workarround to acheive this 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.