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

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'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. 😞