Need to find the average of all values in pivot table

Question

I have a data table that is pulling all successful contacts by case manager over a period of time. We've created a pivot table of the data that groups by case manager for the rows and by month of the contact for the columns. It counts the appointment IDs to get counts per case manager per month.

My manager wants this data displayed in a chart to easily visualize the data. The part I can't get is she wants the average contacts per month over all the case managers and all the months to be displayed on the chart. Essentially, she wants the average of all the values in the pivot table.

I've played around with power pivot and the DAX functions averagex() and summarize() to get averages in the total row per column, but the ultimate grand total is still the average of the totals.

How can I get the average of all the fields by itself?

Here is the sample pivot table with the totals per case manager per month

Pivot Table

Here are the totals using averagex(summarize()) to get counts for the values and average for the totals.

AVERAGEX ( SUMMARIZE ( Table1, [Caseload], "x", COUNTA ( [Client ID] ) ), [x] )

However, the real average I want to see is 34 (all the values averaged together

With Grand Totals

Some example data and the desired results would help a lot. This is not an easily answerable question without specifics. — Alexis Olson
added pictures. Please let me know if I can give more information. — Dave Nicoll
Can you please show your current measure and what it's giving you that you don't want? — Alexis Olson
I would like the 116 to show 34 instead, and the grand totals column on the end I would like to be averages of the monthly columns as well (so 52 for Ashley instead of 206) — Dave Nicoll
Current measure is =averagex(summarize(Table1,[Caseload],"x",counta([Client ID])),[x]) — Dave Nicoll

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-04T22:43:47

You need to summarize by month as well to be able to average the way you want.

avg_count =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[Caseload], --Assuming this is what you have on the rows
        Table1[Month],    --Assuming this is what you have on the columns
        "x", COUNTA ( Table1[Client ID] )
    ),
    [x]
)

Need to find the average of all values in pivot table

1 Answers