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

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

  • utsavlexmark's avatar
    utsavlexmark
    Helper III
    7 years ago

    Hello Maggie,

    Just wow; I could not explain - how happy I am. I was knocking my head in wall to do this for last one month.

    Thank you very much.

    Not only, this trick will help many more in future.

    Thanks from all of them in Advance.

    Regards

    Utsav

  • utsavlexmark's avatar
    utsavlexmark
    Helper III
    7 years ago

    v-juanli-msft, after a long time I need to resume this post again. Initially I thought this process is working perfectly - but now I found some discripencies are there in the process.

     

    Let me try to explain the situation; currently I have included data till Week52(current week). When I am creating the table Week 52, Week 51, Week 50 and Week 49 is showing. I have created one column for avarage of last 4 weeks. We have one process to validate this data and forund in the table data are matching. But the Avearge of Last  4 Weeks is not matching.

     

    When I downloaded the Dashboard data, I found Downloaded data of Week 50 is not matching with what I saw in table and that is the reason the last 4 Weeks Average is not matching.

     

    Detail analysis is showing that this problem is occuring randomly for different weeks.

     

    I guess you defenitely have a solution for this issue.

     

    Hope I am clear with the explation - if not please let me know.

     

    Regards

     

    utsavlexmark(Utsav)