Show Individual Performance vs. Team Average with Slicer

[amhiggins]

Hello Community!

Overview: I am trying to show the average count of activities completed by person by hour for an entire team versus a selected individual on the same plot using a slicer. 

My data looks something like this. Each activity has a unique id and I want a count for each individual in each hour and then the average per person per hour. 

The individual count is pretty straight forward - just a distinct count of id. I then created a measure to calculate the average per person per hour which works fine when all the team members are selected but it changes to only show one team member when one team member is chosen. Can someone help me figure this out? I feel like either my measure is wrong or I need some fancy data model that I'm just not seeing. 

Here is the measure for average count per person per day that I'm using. I added the ALL() to try to ignore the slicer for this measure but that is not working (is the ALL() in the wrong place?).

Avg by Person by Hour = AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Owner],
        'Table'[Actual End by Hour],
        "Hourly Count", CALCULATE(DISTINCTCOUNT('Table'[id]), ALL('Table'[Owner]))
    ),
    [Hourly Count]
)

When all people are selected, I get the count I want:

(Note, these plots are showing a single day)

But when I select just one person the average measure changes to just be that one person.

Any help is very much appreciated!

[MFelix]

Hi @amhiggins ,

You need to use the all in the summarize not in the CALCULATE. Measure are based on context and you want to calculate the values for each of the persos within the selection, what you are doing is first select the filtered values by person and the calculate the values for all the selection.

You need to do the contrary so first select all the data and then do the calculation:

Avg by Person by Hour = AVERAGEX(
    SUMMARIZE(
        ALL('Table'),
        'Table'[Owner],
        'Table'[Actual End by Hour],
        "Hourly Count", DISTINCTCOUNT('Table'[id])
    ),
    [Hourly Count]
)

Be aware that you may need to use ALLSELECTED instead of all because of the transiction context:

Avg by Person by Hour = AVERAGEX(
    SUMMARIZE(
        ALLSELECTED('Table'),
        'Table'[Owner],
        'Table'[Actual End by Hour],
        "Hourly Count", DISTINCTCOUNT('Table'[id])
    ),
    [Hourly Count]
)

[amhiggins]

Thanks for the response! When I move the ALL up in the summarize I lose the granularity of the calculation by hour - it just gives me a single average calculation that is the same for every hour - is there any way to keep the hour to hour calculation?

I also have some other filters that I'd like to preserve on the data (such as the date) so I tried using the command below but doing this causes the value to change when I use the Person slicer - am I using ALLEXCEPT incorrectly?

Avg by Person by Hour = AVERAGEX(
    SUMMARIZE(
        ALLEXCEPT('Table', [OTHER-FILTERED-COLUMNS...]),
        'Table'[Owner],
        'Table'[Actual End by Hour],
        "Hourly Count", DISTINCTCOUNT('Table'[id])
    ),
    [Hourly Count]
)

[MFelix · Accepted solution]

Hi @amhiggins ,

Try the following code:

Avg by Person by Hour =
AVERAGEX (
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[Actual End By hour],
            'Table'[Owner],
            "@IDCount", DISTINCTCOUNT ( 'Table'[ID] )
        ),
        ALL ( 'Table'[Owner] )
    ),
    [@IDCount]
)

[amhiggins]

Sadly, that calculation changes when I select a person in the slicer. 😞

[MFelix]

Hi @amhiggins ,

Can you share a sample file? I made a test file and work properly.

[amhiggins]

I went to create the sample file and found that one of my other filters on the page was causing the problem with your last suggestion - It looks like the last formula will do exactly what I want!!

Thank you so much for your help!