Forum Discussion

Timmo1016's avatar
Timmo1016
Regular Visitor
1 year ago
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 are Regions B, C, and D. Can I somehow upload an excel file here? It does not accept xlxs or csv files). I want to be able to show this quarterly by region on a bar chart (Region A quarterly average for Q1 2022 should be 13394). 

 

I can't seem to figure out how to create this measure. I understand that the numbers in this data are going to always average out to whatever the last month in the quarter is, but this is just initial dummy data. Going forward, the monthly numbers will be changing. 

 

Ideally, I would want this number expressed in 1000s of employees. (I.E. Region A quartely average for 1000s of employees is 13.39) 

 

I was able to do this in tableau relatively easily, so I am sure it's possible in PBI. Any help would be appreciated! 

 

 

DateYearMonthRegion# of Employees
12/31/2021202112A12906
1/31/202220221A13394
2/28/202220222A13394
3/31/202220223A13394
4/30/202220224A14018
5/31/202220225A14018
6/30/202220226A14018
7/31/202220227A14569
8/31/202220228A14569
9/30/202220229A14569
10/31/2022202210A15216
11/30/2022202211A15216
12/31/2022202212A15216
1/31/202320231A15396
2/28/202320232A15396
3/31/202320233A15396
4/30/202320234A15522
5/31/202320235A15522
6/30/202320236A15522
7/31/202320237A15754
8/31/202320238A15754
9/30/202320239A15754
10/31/2023202310A16083
11/30/2023202311A16083
12/31/2023202312A16083
1/31/202420241A16057
2/28/202420242A16057
3/31/202420243A16057
4/30/202420244A16057
5/31/202420245A16057
6/30/202420246A16727
7/31/202420247A16905
8/31/202420248A16905
9/30/202420249A17006
10/22/2024202410A17006
Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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.

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    Hi Timmo1016 

     

    As you select the type of your date , in date format you can easily be able to show that column in date hierarchey of year-quarter. as you add this column in x-axis of bar chart, select date hierarchey and remove day and month. and dont forget to expant the visual .

     

    you can add regions to the legend and for the y-axis you need to write a measure as follows:

    measure _avg := var tbl=summarize ( your_table , date , #employees)

    return averagex (tbl , #employee) /1000

     

    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.