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

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

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)