Hello. I'm looking for some advice on how to create a fixed average (or other aggregate measure) that ignores a filter. I know you can create a fixed aggregate that works in table format (Calculate total ignoring filters ), but I want to be able to graph this so that the fixed aggregate measure (in this case, the average) appears as one value in the same chart (see below image).
In the attached sample workbook, I've used the "state" dimension in a secondary data source ("Olympic States") as a filter on the "state" dimension in the Superstore data source. I want to be able to show how these Olympic States compare with the average of all states (and not just Olympic States) against the "Quantity" measure.
Any ideas?
Hello Kylie,
I wasn't clear about your limitations so I went ahead and created one potential solution.
First, I created a field named Avg State Quantity] and set this to: Avg({INCLUDE [State],[Category]:sum([Quantity])}) to display the outcome of the All States example.
Next, I created the Olympic Quantity with the field: If [State]="California" or [State]="Utah" or [State]="New York" or [State]="Missouri" or [State]="Georgia" then [Quantity] end
Finally - I created the field for just the Average of Olympic States - Avg({INCLUDE [State],[Category]:sum([Avg Olympic States Quantity])})
I then put this on the view side by side.
I hope this gets you started. I saved this as version 10.2.
Patrick
