Forum Discussion

amhiggins's avatar
amhiggins
Frequent Visitor
5 years ago
Solved

Show Individual Performance vs. Team Average with Slicer

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...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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's avatar
amhiggins
Frequent Visitor
5 years ago

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's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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's avatar
    amhiggins
    Frequent Visitor
    5 years ago

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

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      5 years ago

      Hi amhiggins ,

       

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

      • amhiggins's avatar
        amhiggins
        Frequent Visitor
        5 years ago

        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!