Forum Discussion

utsavlexmark's avatar
utsavlexmark
Helper III
7 years ago
Solved

Looking for Tips on Storing Data

Hello, I am an Digital Marketing Professional and recently started using Power BI for representing data to the stackholders. Currently I am working with a simple data which I need to present with lo...
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi utsavlexmark

    Create measures in your table

    measure =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) 
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure1 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 1
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure2 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 2
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure3 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 3
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)
    current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)

     

     

    Best Regards

    Maggie

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

utsavlexmark If you can post some sample data and expected output that will be helpful to understand better.

  • utsavlexmark's avatar
    utsavlexmark
    Helper III
    7 years ago

    Hello pattemmanohar,

    Thanks for your reply. Here I am trying to provide the info you need. Please let me know if you have more doubts, I would love to explain.

     

    Database

     

    Partnumber

    Mktname

    Totquantity

    Week

    Year

    A0001

    Product A

    1

    42

    2018

    A0001

    Product A

    2

    40

    2018

    A0002

    Product B

    2

    42

    2018

    A0002

    Product B

    1

    39

    2018

    A0003

    Product C

    1

    42

    2018

    A0003

    Product C

    2

    41

    2018

    A0004

    Product D

    2

    42

    2018

    A0004

    Product D

    1

    40

    2018

     

    Output

    Slicer 1=Week=42(selected)       Slicer 2=Year=2018(selected)

     

    Partnumber

    Mktname

    Totquantity(Week39)

    Totquantity(Week40)

    Totquantity(Week41)

    Totquantity(Week42)

    A0001

    Product A

    0

    2

    0

    1

    A0002

    Product B

    1

    0

    0

    2

    A0003

    Product C

    0

    0

    2

    1

    A0004

    Product D

    0

    1

    0

    2

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

      Hi utsavlexmark

      Create measures in your table

      measure =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) 
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure1 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 1
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure2 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 2
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure3 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 3
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)
      current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)

       

       

      Best Regards

      Maggie

      • utsavlexmark's avatar
        utsavlexmark
        Helper III
        7 years ago

        Thanks Maggie,

        Let me try all these tricks - I hope will be able to implement as directed by you, I fI found any issue - will let you know.

        Regards

        Utsav

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      7 years ago

      utsavlexmark That means, if you select week 40 then you want to display week 39 and week 40 data only isn't it ? 

      • utsavlexmark's avatar
        utsavlexmark
        Helper III
        7 years ago

        No, what I want is - when I will select a week in Slicer - in the table data of that week and preceeding 3 weeks will be shown. This is actually to see how a particular product is performing in last 4 weeks including the selected week.