We need another function to help us calculate the virtual table again, so there you go:įact_Table, Fact_Table, Fact_Table. But notice that, the returning result of SUMMARIZE will be a table view instead of a single figure. The technique here is group all the columns which are part of your slicers in your Power BI report page expression will be the calculation whatever you want it-in this case, DISTINCTCOUNT the EmployeeCode column. SUMMARIZE function is perfect when we want to build a temp table by using grouping function. If we can build a temp table which only has two columns (Month of the year, Headcount per month), our next step will be a lot easier. Establishing a virtual middle table by grouping those key dimensions is the first good thinking. Our next step is how we can turn the fact table (Table02) into Table01. Note: I’ve simplified the fact table structure by cutting out other measurements.) Firstly, I need to know how many employees submit their timesheets every month. Rolling average headcount means averagely, how many employees have been working in the past few months. And it seems none of them applies to my scenario. It is…overwhelming for someone whose DAX was not her strongest skill. When I started figuring out how to calculate Rolling Average headcounts, I read a lot of blogs regarding the DAX codes. You might have seen a lot of articles teaching you how to calculate Rolling Average (sales, quantity, etc.). Comparing my actual headcount and budget headcount against employee productivity, so I can tell whether the department or team is under-performing or over-performing. It is important because each department or team might have a budget headcount. Recently, I got a request from my client asking me to get the average headcount up for the current month. Therefore, an average rolling headcount becomes an essential KPI for business managers overseeing business performance. Business leaders might want to know what the headcount is and whether the headcount is the reason affects their business performance. Hence from month to month, the employee headcount varies. For median to big organisations, employee turnover is a common phenomenon.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |