Average Calls per Day DAX help

[deboec]

Hi,

I am looking for a DAX code to calculate the average amount of phone calls per day.

My data looks like that:

Table 1:

Call ID	Date	User ID	BU ID	Weighted Call
1	01/01/2021	01	1	0.75
2	01/01/2021	02	2	0.3
3	01/01/2021	01	1	1
4	01/02/2021	01	1	0.15
5	01/02/2021	02	2	0.3
6	01/03/2021	03	3	1
7	01/03/2021	03	3	0.9
8	01/03/2021	02	2	0.8

So far I made a measure for the sum of Weighted Calls:

Weighted Calls =
SUM(
    'Table_1'[Weighted Call]
)

Want I want do display in Power BI via matrix visual is the average (weighted) calls per day for User or BU.

I made a measure for the average Calls:

Average Weighted Calls per Day =
AVERAGEX(
    VALUES(
       'Table_1'[Date]
    ),
    [Weighted Calls]
)

If a go ahead and make a matrix visual to show the BU ID on a row level and the [Average Weighted Calls per Day] as Values the result per row seems to be right but the Total row on the bottom is showing the sum of the different averages per row (BU ID) and not the total average across all data; for example:

BU ID	Average Weighted Calls per Day
1	4.3
2	5.1
3	5.8
Total	15.2

What do I need to do with my measure to display the correct Total in the Total Row?

Thanks

[daxer-almighty · Accepted solution]

[deboec]

Thank you very much.
That was exactly what I was looking for.
Obviously I tried to be as precise as possible to get an efficient discussion going but unfortunately could not manage to express my exact needs to do so.

[Anonymous]

If you had said: "I want an average over BU's of averages over days.", that would have been straight to the point. It would have been as clear as the Sun. Nothing more needed but knowing what the model looks like.

[deboec]

In retrospect it makes a lot of sense to communicate it like that. Don't worry I'll try my very best to be more precise next time. I wasn't trying to be imprecise on purpose, obviously.

[daxer-almighty]

[deboec]

I assume the problem is a conversion from my German units to the English client.
(In German we use the comma , as a dot . just the exact opposite way as in English writing).

So assuming the values in the table on your left are right I am looking at the value "44.37" as the "Total Daily Avg".

The result I am looking for is the Total Daily Avg across all BU IDs which actually is something like the "average of the averages" instead of the "sum of the averages"

[Anonymous]

That's what I get using your data...

[deboec]

Thanks for your reply!
If I use my sample data and your DAX code I also get correct results.

However if I use my real world data I do not get correct results.

I included my sample data in this Google Drive link:

Google Drive Sample Data