Listed below the problem I am facing with Context filters and LOD calculations to get the the top store sales. Please suggest.
1. List Top 20 stores which has higher sales as bar chart
2. In the secondary axis, show the avg district sales for the store which it belongs to (line chart)
3. Add a reference line to show avg regional sales.
4. Include the filters to select Region & District.
5. On the view open, dashboard to list Top 20 stores at the company level without any filters applied
6. Upon the region selection the report to display Top 20 store sales of the selected region, however the Avg Company and district sales to be remain same.
Following is the Hierarchy (Region - > District -> Store)
1. I am able to create dashboard at a company level. Both Region and District Avg Works fine.
2. However when I apply the filer Region -> e.g East. Avg regional sales line data getting recalculated to the specific region, however the requirement is to show for entire company avg. i.e Avg of all Regions. I have used Fixed LOD to calculate the Avg Regional sales and district sales.
3. I have context filters on Region and District - Because the 20 stores should be displayed based on the filter selection
4. Understand that context filters is causing this issue, because the fixed LOD will be applied after the context filters.
Please let know if there different approach for this.
Do you have a workbook showing what you have tried with some sample data? You will find the community will engage more actively when submiitting a question with a packaged workbook that shows where you are stuck and what you have tried.