Employee Count by Month

[smullan]

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 

[Anonymous]

@smullan,

Could you show more details, please? I did not understand your problem. 

[smullan]

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

[Anonymous]

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!