Forum Discussion

machineReadable's avatar
machineReadable
Regular Visitor
7 years ago

Having Trouble Creating a Rolling Aggregated Average

I feel like this should be simple, but my brain just isn't working today. In the image below, I have a pivot table with the Headcount measure (which itself is a Countrows() within a Calculate()) agg...
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
6 years ago

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1Column2
A1
B2.5

Also, what is the syntax for [Headcount]?

machineReadable's avatar
machineReadable
Regular Visitor
6 years ago

Headcount = COUNTROWS ( SUMMARIZE ( FactEmployment, FactEmployment[Employee Key] ) )

FactEmployment is a factless fact table that has a row for each employee, for each date they have been employed, and the role they are in, team they are on, etc. This way in my pivot table, I can group the headcount by those dimensions, and it reacts to any date range I select.

In this case I'm just doing a simple count, but using a table like this has been helpful in the past to create measures and use the entire table as a filter in the measure, although it is very resource intensive, so I'm looking for alternatives.