Hi all;
I am working with a sales report, below is a picture of the diagram view (a picture of the pivot table is further down):
YrWk = This is a concatenation of the year and the week number
Rating = This is the performance of the item during each YrWk
I need to create 3 measures to get the below results and all 3 measures need to repeat in each row on the pivot table:
1) The maximum rating of the item during the selling period.
2) The YrWk of the maximum rating. Note: if the maximum rating happens multiple times then I need to get the latest YrWk.
3) The sales amount on the YrWk of the maximum rating.
I have uploaded the excel file I am working with on to dropbox for easy download (328 KB):
https://www.dropbox.com/s/t2wqed1kbrshhr9/RatingReport.xlsx?dl=0
I would like to get the results as shown in the grey columns of the below picture:
Notice that for item 2678 the maximum rating (100) happens multiple times and I am getting the last YrWk it happened (201646).
I am having problems figuring out how to get the point 3 above and make the results repeat in each row. After spending hours trying to solve this and going nowhere I decided to get some help. Any help or suggestions will be appreciated.
Thanks.
I am working with a sales report, below is a picture of the diagram view (a picture of the pivot table is further down):
YrWk = This is a concatenation of the year and the week number
Rating = This is the performance of the item during each YrWk
I need to create 3 measures to get the below results and all 3 measures need to repeat in each row on the pivot table:
1) The maximum rating of the item during the selling period.
2) The YrWk of the maximum rating. Note: if the maximum rating happens multiple times then I need to get the latest YrWk.
3) The sales amount on the YrWk of the maximum rating.
I have uploaded the excel file I am working with on to dropbox for easy download (328 KB):
https://www.dropbox.com/s/t2wqed1kbrshhr9/RatingReport.xlsx?dl=0
I would like to get the results as shown in the grey columns of the below picture:
Notice that for item 2678 the maximum rating (100) happens multiple times and I am getting the last YrWk it happened (201646).
I am having problems figuring out how to get the point 3 above and make the results repeat in each row. After spending hours trying to solve this and going nowhere I decided to get some help. Any help or suggestions will be appreciated.
Thanks.