Forum Discussion
Employee Count by Month
Hi,
I have a field in Power BI that counts the total number of employees based on their employee number - this is captured every couple of days.
When I then filter this employee count by month, or even by year, it is unclear what count Power BI automatically selects. Does anyone know how Power BI selects a particular value for a month filter for example?
Thanks
3 Replies
No problem, apologies for the confusion!
I have a table similar to the below in Power BI. If I select a matrix visual and put 'Headcount' in 'Values' and use the timeframe as the Column, selecting 'Month' in the Date Hierarchy, how does Power BI determine which headcount figure to pull in when, for example in January, there are 11 separate figures for this month?
Headcount Date 300 26-Dec 302 29-Dec 301 01-Jan 380 04-Jan 383 07-Jan 390 10-Jan 391 13-Jan 389 16-Jan 389 19-Jan 390 22-Jan 380 25-Jan 383 28-Jan 390 31-Jan 392 03-Feb 388 06-Feb 387 09-Feb 388 12-Feb Thanks
What are your columns data type?
In my case, Headcount is Whole Number and Date is Date
Table
If you are using the same type as my example, creating a Matrix visual and adding Headcount as Values and Month as Columns, you will get a visual like this:
The sum for january is 4166, which is the sum of the eleven figures that you said. If you add Month and Day as columns and Expand all down on level in the hierarchy, you cand see the values:
Now, if the column Headcount's type is text and you put it in the matrix' values, the predefined order is to order by first value. And what does it means? Take a look on this example:
January has two distinct values for Headcount ( 222222 - 300000) and the first Headcount 222222.
December has tow distincts values for that column (111111 - 2) and the first Headcount is 111111.
This happens, since the values of Headcount are strings, it is ordered by lexicographical order.
You can read about lexicographical order here and here.
Hope it helps!
