Hi everyone! I'm wondering if anyone here is able to guide me in the right direction regarding an issue I've been having. I am no expert in Power BI so I'm almost certain that this issue has an easy solution but I haven't been able to find it in these forums or elsewhere.
I have a database with data of different units within a hospital, and how many patients of different ages they see every month. In this database, each row if a different age at a different hospital unit on a different month, and then there is a column that expresses the number of patients of that age that were seen in the specific hospital unit on the specific month. I attach an example of the date, with all identifiers removed:
| Month | Unit | Age | Number of patients |
| January | Heart surgery | 0 | 2 |
| January | Heart surgery | 1 | 6 |
| January | Heart surgery | 2 | 4 |
| January | Heart surgery | 3 | 11 |
| January | Heart surgery | 4 | 9 |
| January | Psychiatric unit | 0 | 0 |
| January | Psychiatric unit | 1 | 0 |
| January | Psychiatric unit | 2 | 3 |
| January | Psychiatric unit | 3 | 8 |
| January | Psychiatric unit | 4 | 19 |
| February | Psychiatric unit | 0 | 0 |
| February | Psychiatric unit | 1 | 0 |
| February | Psychiatric unit | 2 | 1 |
| February | Psychiatric unit | 3 | 5 |
| February | Psychiatric unit | 4 | 14 |
What I'm trying to do is have a card in my Power BI report that shows the average age of all patients seen by the unit that the user chooses (through a slicer), on the month the user chooses. So in the example above, the average age of all patients seen by the Heart surgery unit in January should be 2.59.
No matter how hard I try, I can't get Power BI to produce the right average. I suspect that the problem is linked to the fact that one of the age groups is 0, and some age groups have a number of patients that is = 0, which seems to 'throw off' most average calculations. I've tried using measures in Power BI and also going over to Power Query to create columns that should calculate the average correctly, but in the end I just can't get the right average in my cards. What could I be missing here? How would you guys go about producing this average?
Thanks a lot in advance!
