cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smullan Frequent Visitor
Frequent Visitor

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 3
caaarlos Member
Member

Re: Employee Count by Month

@smullan,

 

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

smullan Frequent Visitor
Frequent Visitor

Re: Employee Count by Month

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?

 

HeadcountDate       
30026-Dec
30229-Dec
30101-Jan
38004-Jan
38307-Jan
39010-Jan
39113-Jan
38916-Jan
38919-Jan
39022-Jan
38025-Jan
38328-Jan
39031-Jan
39203-Feb
38806-Feb
38709-Feb
38812-Feb

 

Thanks

caaarlos Member
Member

Re: Employee Count by Month

What are your columns data type? 

 

In my case, Headcount is Whole Number and Date is Date

image.pngTable

 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:

image.png

 

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:image.png

 

 

 

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:image.png

 

 

image.png

 

 

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!

 

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 367 members 4,436 guests
Please welcome our newest community members: