we are working on improving template Excel Dashboards for a product we are developing that will use Excel as the front end "reporting/dashboarding" tool.
We have created a version of the template to calculate variances of sub-group to group averages. For example the over/under performance of age bands in sales for categories. In this version we are using a separate workbook. We are trying to move these calculations into the pivot table that provides data to that worksheet.
By default when we create a measure for average, the average is calculated for each subgroup and that is not what we want. what we want is an average recalculated for each group and repeated in each row of the pivot table so that we can measure the variance.
As you see in the picture ( Which is in the excel file) , we managed to create this measure ( Last column) and wei also brought in the total average into the pivot table so that you fully understand what we mean.
The problem with my measure is that we get the labels for subgroups where we have no sales in them ( we used All function in the filter argument of the calculate function to get the repetitive average). and therefore we get redundant fields in the chart with no bars in it and that makes my chart and also pivot table incorrect.
Would anyone here know of an example video/excel workbook where the variances of sub group to group are calculated properly and then put on a chart? Where if the number of sub groups change the chart will respond properly?
As I said, we already did this once by doing the variance calculations in a separate worksheet and then using offsets etc to collect the correct number of fields to place into the charts.
But my Excel developer thinks that we can improve that design by doing the calculation inside the pivot table using measures.
Any advice you can offer would be much appreciated