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. 

Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 337 members 2,938 guests
Please welcome our newest community members: