Employee hierarchy count bar chart?

[skaha7]

Hi All,

Bit of a newbie to this so I'm not sure if this is the appropriate place to ask. It's the visual I'm hoping for but could also see it potentially as a DAX related question or maybe even a data model question? So please feel free to punt me in the right direction if this is the wrong spot!

My data will look similar to below, mainly employees, department and divisions:

Employee	Department	Division
Bob	Dept A	Divison 1
John	Dept A	Divison 1
Mary	Dept B	Division 2
Sue	Dept B	Division 2
Carson	Dept C	Divison 1
Aaron	Dept B	Division 2
Norman	Dept A	Divison 1

What I'm hoping to do is to visualize all three levels on one bar chart. similar to something like this: (it's a mock up and for sure not accurate!)

Where I struggle is I think to have all three together I would need to "double/tripple" count the employee in order for it to count on all three bars? 

My first thought was to union my table together three times and create a single column for dept/division/company but that "feels" ineeficient? 

So I guess I'm wondering if there's a better way to do this? 

[Anonymous · Accepted solution]

Hi @skaha7 ,

Perhaps you could try the following.

The data source is as follows.

Select the Employee column and then unpivot the other column

You can then rename the Attribute column and the Value column to get the following result.

Then select the cluster's column chart visual and put in the appropriate columns.
In this way Department, Division, and Company hierarchies are put into the chart.

Please see attached pbix for reference.

Best Regards,

Dengliang Li

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

[dharmendars007]

Hello @skaha7 , 

You can just write 3 measure each filtering  "Dept", "Div", "Comp" and add the same to the Bar chart it will work..

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[skaha7]

Thanks for responding! 

I think I might be missing something. If I filter for Department A. It will limit the row counts for Div and Comp as well won't it? 

I could Hard code the company count since that wouldn't change. 

But filtering between Department A / B / C should also change between Division 1 & 2, but just at a different grain?