Filtering the best week and getting the sales amount

LBala

New Member
Joined
Apr 3, 2014
Messages
22
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):

154bxj6.jpg



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:

2btsf7.jpg


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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top