Forum Discussion
Helper IAverage Calls per Day DAX help
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
8 Replies
- daxer-almighty
Solution Sage
deboecI 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"
- daxer-almighty
- 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.
- 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:
