Show an average of the selected members of a filter.
Hi, I would like to be able to show the average of some measures I have calculated. My scenario is as follows: I have some measures shown in a line graph over time. This measures are in my Trellis vertical so I have a line graph for every measure and in every graph I show the measure of the groups I have in a multi-select drop down filter. What I would like to add to every graph is the average of the measure it is showing, but I have only been able to show this average as another graph. If I try to add this measure as an y1 or y2, it shows me the average for every group and I only want to see the average of all the groups that are selected. Is there a way to include this dynamic average?
Thanks
8 replies
-
please provide some examples of what you're trying to do - maybe via some images.
-
This is what I have so far. In the first graphic I have a measure for the groups I have selected in the filter. In the second graphic I have the average of that measure. I want to have both graphics in just one, but if I move the average as a second y1 or as a y2, it shows me the average for every member I have selected. I hope this helps. Thank you in advance for answering.
-
-
This is not currently possible - because the underlying query does not allow for 2 levels of granularity in the same response.
I believe there is a feature request on the forum for this already.
-
Hi Jair,
Yes, you can do this quite easily in fact. The trick is the use of a dynamic list driven by a parameter and a calculated member in the same dimension using the same list to calculate the average.
First, create a parameter containing the members of the dimension you want to include in our analysis. In my example here, I've created a parameter containing five countries:
Next, build a dynamic list of countries that contains just the parameter selected:
Finally, build a custom member in the same dimension as your selected members, in this case Countries, which is an average of the dynamic list.
Note that the Data point option in the green box is not selected. This allows you to choose any measure in your analysis and the calculated member will return the average of that measure. Also that the calculation is in the Countries dimension, not measures. It effectively becomes another member of countries alongside the real ones.
In your analysis, select the dynamic list and the custom average member to be displayed and make the parameter multi select. Place Countries in the Color Drop Zone and choose your measure to analyse. I've colored the average in red and made it a thicker dashed line to show up better
At run time, you can choose the members to analyse from the parameter. These values are injected into your dynamic list and get displayed individually on your chart. The custom member calculating the average is driven from the same list, so is always calculated using the same selected members.
Hope that helps!
Ian
-
Totally what I was searching for! Thanks a lot!
-
Just make sure when you create the parameter to mark it as a multi select Parameter :
-
-
Thanks for the support, hope this helps more users with their analysis!
