Can anyone help me to solve the following problem?
We have to create a report which shows a difference between two kind of quantity on different aggregation level from the same data table with filtering options. We already tried the OVER statement in the calculated columns but it doesn't taking into account the filters what the user can set it.
I have linked a sample table which is similar to our data table. The goal is that the user can select that the graph shows the figures on the selected aggregation level but filtering as well on the available filtering options.
The aggregation levels are the Level 3, Level 4, Material and Package. The filters are all of the columns.except the figures (Forecast Quantity, Requested Quantity, Delta and Absolute Delta).
The graph should be showing the Requested Quantity and the percentage of the Absolute Delta and Total Forecast Quantity on the selected aggregation level.
Our problem is that the Package and the Customer fields are not in the Material Hierarchy therefore it brakes the Absolute Delta figures and cause incorrect value if we include these fields into the OVER statement with the aggregated value.
My main question is that how can a calculated column with Over statement be dynamic which taking into account the filters?
Additional information: The material is in a hierarchy which included in the second sheet. On the different aggregation level, the Delta should be the difference of the sum of the Forecast Quantity and the Requested Quantity and the absolute is the absolute of the previously detailed Delta.
Sample file: https://drive.google.com/file/d/0B6T-8YUYuuSEb3pQN29JRU5QeGs/view?usp=sharing
Have you considered using the OVER statement directly when displaying and not pre-computing a new column?
If you use the OVER statement directly in the axis property, the data will be computed as the display is generated, so the results will respond to the user's filters.
This dynamic calculation will also work for things like Error bars, then your error bars will respond to the user's filters as well.:
...Finally if you need to change the calculation dynamically, there is a way to do that as well, which I can explain if you need.