Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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