Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

ALL function not working

Hi!

 

I am trying to utilize the ALL function in a real life scenario, but it dosen't seem to work.

I have a data model in which I have added a calculated column to see how far a list of around 600 teams have come in a process. It's either 0, 25, 50, 75 or 100%. Each team also belongs to a special team-group, which on their part also belong to a work area. So it's an organization like this: work areas<several teamgroups<even more teams.

 

I have made a measure which calculates the average progress (it's a %): = AVERAGE('team status[Progress Team %]). The value displayed from this measure changes when I press on different work areas or work groups in a bar chart. For example, all teams under workgroup X might have an average progression of 5%, while workgroup Y has 9% progress on average.

 

I would like to compare this dynamic value to the average for ALL teams in the whole organization, which I for a fact know is 7%. The two values could be compared in a Gauge chart for instance, where the average for ALL teams is the taget value. When I make a measure like: = CALCULATE(AVERAGE('team status[Progress Team %])), ALL('team status')) the percentage still changes from 7% to 5% when I select workgroup X, and so on, despite me using the ALL-function. How do I make this work and have a value that tells me the average without changing by filter context?

 

Thanks for the help!

Need Help

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    What is the structure of the model and the table?
    In the following example, with this sample table

    and these measures

    Average Points = AVERAGE('fTable'[Points])
    All = CALCULATE(AVERAGE(fTable[Points]), ALL(fTable))

    I get

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      The table is very similar to the one you have. Here is a picture of the calculated column: 

      On each row there is also a column for team, team group, and an area, but with other names I can't share. 

       

      As you can see the data model consists not only of 0% values, but also 25%, and 75%. No team has come to 50% or 100%, that's why those values don't exist. 

       

      I've made the exact same formulas as you have written now. These are the results:

      It looks promising! When I copy and paste the same matrix onto another page with a page level filter for a special team group, it looks like this:

      Now all of a sudden, it shows the average progress for the team on both the average AND the ALL value! This is what I don't want. I want the ALL value to still display 6,87% which is the average across all teams in the company. 

       

      Any way to solve this?

       

      BTW, the page with a page-level filter is reached by using drill through.