Forum Discussion
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 lots of complicated calculations. Here are the headings of data I am working with:
partnumber | mktname | totquantity | Week | Year |
|
|
|
|
|
Week and Year is for Slicer; main issue is with other fields.
“totquantity” field contains data of how much units have been sold and all the data is flagged with week and year. Now let me explain what my requirement is:
Suppose I select Year “2018” and Week “42” in slicer; “totquantity” will show week 42 data naturally – along with that I want 3 other columns will be there where I can see “totquantity” of Week 41, Week 40 and Week 39 respectively.
First of all I don’t whether I able to explain the situation properly or not; if not please let me know – I will try to explain it as much as possible.
Secondly, I don’t whether the requirement is technically possible or not.
I am open with all type of reply.
Regards
Utsav
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
8 Replies
utsavlexmark If you can post some sample data and expected output that will be helpful to understand better.
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
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
