How to get daily averages grouped by month

[nunogand]

So, I'm a beginner in PowerBi and although I've managed to find my way around Excel in PowerBi I'm still having some difficulties.

Right now I'm having a lot of trouble getting something that seems very simple but really shouldn't be. My data table has several columns but the relevant ones are the ones I show below.

Date	category	events
01/01/2023	a	2
01/01/2023	a	1
01/01/2023	b	1
01/01/2023	c	3
02/01/2023	a	1
02/01/2023	b	1
02/01/2023	b	2
03/01/2023	a	2
…	…	…
31/12/2024	d	1

Throughout the year and for each day, various events have been recorded in different categories; sometimes the same event appears several times on the same day, either because it has a different quantity or because it is repeated on the same day.

What I want is to see the daily average for each month and I'm not managing to do that. I tried something very simple with a quick measure but the value I get is "strange": it's not the monthly, weekly or daily average. It's a number that comes from I'm not sure where (in Excel I can do this operation).

Basically what I want is the sum of all the events that occur in the month divided by the number of active days in the month and to be able to show this in a visualization - and in this visualization I can use the visualization filters to show only one or two categories. I can't use COUNTROWS to calculate the days because the same day is often repeated. In my head it sounds very simple but I'm missing a lot of DAX.

Any help or at least could you point me in the correct track?

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Ceate a Calendar Table with calculated column formulas for year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To your Table visual, drag Year and Month from the Calendar Table
4. Write these measures

E = sum(Data[events])

Days in month = countrows(Calendar)

Measure = divide([E],[Days in month])

Hope this helps.

[nunogand]

Actually, there was an error:

I can't just use Days in month = countrows(Calendar) because some of my calendar days don't have any data on them. What I did instead (and solved the problem) was using DISTINCTCOUNT('Data'[Date]). That way I counted only the individual days with associated data.

[nunogand]

That did the trick.

Thanks.

[Ashish_Mathur]

Hi,

Try this approach

1. Ceate a Calendar Table with calculated column formulas for year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To your Table visual, drag Year and Month from the Calendar Table
4. Write these measures

E = sum(Data[events])

Days in month = countrows(Calendar)

Measure = divide([E],[Days in month])

Hope this helps.