Matrix table to show both information and values by month

[Anonymous]

Hello. I have two tables: Employee Information that contains for each employee (identified by ID), their job title and start date; and a Full Time Equivalent value table, that shows headcount values by month, also by ID. 

In a matrix table, I can show IDs in rows, then year month as columns, and the headcount values, therefore headcount values by month by each employee. However I want to add some employee information (e.g. job title), so the table looks like this, with information in columns before the value columns:

Employee ID	Job Title	Start Date	Jan-20	Feb-20	Mar-20
00001	Accountant	Dec-09	1	1	1
00002	Finance Manager	Jul-18	1	1	1

Is there a way to achieve this?

It looks like I can manage to have a tabular table to show the first part, i.e. information, then a matrix table to show the headcount values. But how can I combine them together?

Thanks in advance.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

As far as I know, matrix could not meet your requirement currently in power bi because the value dimension of columns are different. Attached a sample file in the below:

Martrix can only show like this:

You can try to use both table and matrix to adjust their postions to 'combine' them although they could not be combined really:

In addition, if the above not help, you can submit the requirement to ideas and add your comments there to help us improve power bi and make this feature coming sooner: https://ideas.powerbi.com/forums/265200-power-bi-ideas
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I've come to accept that after reading some more posts on similar issue. I can sort of make two tables, one tabular and one matrix like you did. But the question is, how do I make sure that the first column of the two tables are exactly in the same order of people? I mean in this image, the first table shows the employee ID and details, then the second matrix table shows the values. I need a way to prevent the rows of the two tables being misaligned or are in different orders.  

[v-yingjl]

Hi @Anonymous ,

You can sort by Employee ID like ASC or DESC in both table and matrix to make the Id sort as the same.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Is there a way that the first columns in both tables can be synced? By this method, if I scroll down in the lefthand side table only, the values in the righthand side table won't change and would be distorting.

Thanks.

[amitchandak]

@Anonymous , is that your source data?

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

Check if this blog can help - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[AnkitKukreja]

Hi @Anonymous 

You're on the right track and yes you can use the matrix to showcase that. Matrix has the limitation when using text field as value, that you can only use first or last title name.

But, in your case it doesn't matter as against the id whatever you choose first title or last the title would show up correctly as an employee would have only 1 title so it would show up what you need.

Hope it helps. Please let me know if anything else is needed.

Thanks,

Ankit