Forum Discussion
Solved
Aggregate monthly data into quarterly average
Hello, I am pretty new to powerBI. I am attempting to report on quarterly headcount data. My current table (below) is broken out monthly by regions (I am only able to add Region A here, but there...
Hi Timmo1016 ,
It's simple to produce the required output. First, create a calendar table and establish a relationship with your headcount fact table. Then, perform the average calculation as shown below:
I have attached an example pbix file for your reference.
Best regards,
Hi Timmo1016 - I hope in you have seperate date table as like below:
create a calculated table as below:
DateTable = CALENDAR(MIN(Employ[Date]), MAX(Employ[Date]))
Create a DAX measure that calculates the quarterly average of employees for each region, and formats it in thousands
Quarterly Average =
DIVIDE(
CALCULATE(AVERAGE(employ[# of Employees]),
ALLEXCEPT(employ, employ[Region], DateTable[Year], DateTable[Quarter])),
1000
)
Go to the Format tab and adjust the axis, data labels, and chart title to make your visualization clearer.You can also add tooltips that show additional details when hovering over the bars.
attached pbix file for your reference.