Chart Help

[user900]

I have a data file that shows User Name, Status and Department. Using the stacked bar chart, I'm showing the Department on the Y-Axis, count of Users on the X-Axis and Status as legend.  I need to also show a bar for the Enterprise, which essentially adds up the data for all departments. 

Any help would be appreciated.

User	Department
(16 names)	A
(4 names)	B
(20 names)	Enterprise (this is what I need, but there is not a row in the data to show this

[MFelix]

Hi @user900 ,

Don't know how you have the enterprise or if it's more than one but assuming it's only create a tabl with  the departments and a row saying enterprise:

Now add the following measure:

Total values = 
SWITCH(
       SELECTEDVALUE(Department[Department]),
       "Enterprise", COUNTROWS(Users), 
       COUNTROWS(
             FILTER(Users, Users[Department] in VALUES(Department[Department])
                    )
               )
       )

Nowuse the measure and the new table values to do your chart. If you want you can add an additional column to your new table for sorting the departments and have the enterprise at the bottom of the chart.

[user900]

I created the new table and applied the suggested measure. When I pull the fields into the chart, the counts in each individual department is correct, but Enterprise is blank.  I need it to total all the Users in all Departments.   Here's what it looks like.

[MFelix]

Hi @user900 ,

Can you share the metric formula please.

[user900]

[MFelix · Accepted solution]

HI @user900 ,

Delete this relationship and everything should work has you need.

[user900]

I have another request to add a breakdown by the highest status. I created measures to get the counts for each of the 4 statuses. However without a relationship to the new Department table, it shows all the same results for all the departments. Can you help so that I can get the results by department, while still keeping Enterprise-Wide. 

[MFelix]

How are the status related with the line of business 

[user900]

Line of businesss is one table (you suggested I create a table and add a row for Enterprise-wide.) Status is in a seperate table with users.

[user900]

Thank you so much!

[user900]

Total values =

SWITCH(

SELECTEDVALUE('LOB order'[Line of Business]),

"Enterprise-Wide", COUNTROWS('Colleague Data'),

COUNTROWS(

FILTER('Colleague Data', 'Colleague Data'[Line of Business] in VALUES('LOB order'[Line of Business])

)

)

)

[MFelix]

Hi @user900 ,

Base on the syntax everything should be correct, please verify if there is an active relationship between both tables, the table that you created with the enterprise, needs to bve a disconnected table from the other ones on the model.