Hi all,
First of all I would like to say thanks to everyone on the forum. Probably more than half of my Excel knowledge can be contributed to Mr Excel. This will be my first post on the forum.
I created a dashboard that uses a number of pivot tables from different sheets, where all pivot charts are then displayed on one main sheet which is the dashboard. The charts are customizable depending on the selection of the relevant slicer.
I have Excel 2013 (which is pretty awesome), but this dashboard needs to function on Excel 2010, so some of the 2013 functionalities cannot be used. I can also not use PowerPivot because the end user does not have access to it.
I use a "Weekend" slicer to filter for the "Week ending on yyyy/mm/dd". The pivot table is set up to calculate the sum of the selected values. The source data to the pivot table gives weekly averages. When you select one of the "WeekEnds" on the slicer, it gives the weekly average for the selected "WeekEnd". See below for the simplest pivot table in the dashboard, together with 2 slicers:
https://drive.google.com/file/d/0B1O2pIC3qS7GVFJSRmd2aHZqb2M/view?usp=sharing
The problem comes in when you select more than one WeekEnd at once. The requirement is that the pivot table should calculate the average based on the selection of the slicer. For example, if you select 4 WeekEnds, the pivot table should divide the calculated sum/total and divide it by 4. Or if you select 12, divide it by 12.
I have tried using the following:
The reason for the abovementioned approaches is if I can count the selected WeekEnds, I can easily calculate the real average in another field and base the chart on this.
Is there any way to solve this problem in Excel? I do have some limited VBA experience, but I would prefer to keep this as simple as possible for the end user.
The file can be found at: https://drive.google.com/file/d/0B1O2pIC3qS7GOXFub2FMRGtqS2M/view?usp=sharing
First of all I would like to say thanks to everyone on the forum. Probably more than half of my Excel knowledge can be contributed to Mr Excel. This will be my first post on the forum.
I created a dashboard that uses a number of pivot tables from different sheets, where all pivot charts are then displayed on one main sheet which is the dashboard. The charts are customizable depending on the selection of the relevant slicer.
I have Excel 2013 (which is pretty awesome), but this dashboard needs to function on Excel 2010, so some of the 2013 functionalities cannot be used. I can also not use PowerPivot because the end user does not have access to it.
I use a "Weekend" slicer to filter for the "Week ending on yyyy/mm/dd". The pivot table is set up to calculate the sum of the selected values. The source data to the pivot table gives weekly averages. When you select one of the "WeekEnds" on the slicer, it gives the weekly average for the selected "WeekEnd". See below for the simplest pivot table in the dashboard, together with 2 slicers:
https://drive.google.com/file/d/0B1O2pIC3qS7GVFJSRmd2aHZqb2M/view?usp=sharing
The problem comes in when you select more than one WeekEnd at once. The requirement is that the pivot table should calculate the average based on the selection of the slicer. For example, if you select 4 WeekEnds, the pivot table should divide the calculated sum/total and divide it by 4. Or if you select 12, divide it by 12.
I have tried using the following:
- Pivot table averages - this does not work because it calculates the average based on all records: (Sum of relevant category)/(Count of all records of relevant category)
- Pivot table calculated field by counting the number of WeekEnds and the using it to divide the sum - did not work because Count() gives 0 and Counta() gives 1.
- Cubesetcount(Slicer_Weekend) to count the number of selections on the Slicer - gives #N/A.
- Short VBA algorithm to run through slicer and count the ones that are .Visible - produces some Pivot Table error which I could not solve.
The reason for the abovementioned approaches is if I can count the selected WeekEnds, I can easily calculate the real average in another field and base the chart on this.
Is there any way to solve this problem in Excel? I do have some limited VBA experience, but I would prefer to keep this as simple as possible for the end user.
The file can be found at: https://drive.google.com/file/d/0B1O2pIC3qS7GOXFub2FMRGtqS2M/view?usp=sharing
