1

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

null
    • Redeemed from the Dark Side (Power BI)
    • Obee1
    • 2 mths ago
    • Reported - view

     please provide some examples of what you're trying to do - maybe via some images. 

      • Jair_Rangel
      • 2 mths ago
      • Reported - view

       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.

    • Redeemed from the Dark Side (Power BI)
    • Obee1
    • 2 mths ago
    • Reported - view

    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. 

      • Jair_Rangel
      • 2 mths ago
      • Reported - view

      Thanks a lot for the answer. Hopefully soon this will be available

    • Senior Director of Product
    • Ian_Macdonald
    • 2 mths ago
    • Reported - view

    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

      • Jair_Rangel
      • 2 mths ago
      • Reported - view

       Totally what I was searching for! Thanks a lot!

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 mths ago
      • Reported - view

       Just make sure when you create the parameter to mark it as a multi select Parameter :

    • Jair_Rangel
    • 2 mths ago
    • Reported - view

    Thanks for the support, hope this helps more users with their analysis!

Content aside

  • Status Answered
  • 1 Likes
  • 2 mths agoLast active
  • 8Replies
  • 64Views
  • 4 Following