Creating a Visual based on data from another Visual

[asavadians]

I'm slicing up some occupancy data for a building that I manage and have 2 similar tables (same table, different filters applied) to represent two different ways of splitting up the different groups. Example:

HR	5
Sales	10
Careers	17
Careers Mobile	22
Commerce	59

HR	10
Sales	12
Careers	40
Careers Mobile	4
Commerce	19

These numbers don't exist in the source data, I got these from using filters.  My goal is to create a stacked column chart to show the comparisons of the numbers by team.  Is this possible?

[BhaveshPatel]

You should disclose the whole scenario that where are you getting these numbers( Which original dataset). You can explicitly define the filters in the CALCULATE statement to get those numbers. ( Filters can come from slicers or columns or rows etc.). Once you have measure defined, you can compare them in stacked column chart. 

[asavadians]

Hi Bhavesh,

Sorry for the lack of context.  The data that I'm using is a list of all employees and what desk they are occupying with some metadata like their employee ID, email address, etc.

What I did to get the tables was using the employee's cost center number to figure out if the employee is a core member of the team or if they're a support member from a different department.

i.e.

Cost Center is 4111 = Core

Cost Center is 1123 = Embed/Support

By the way, I have a separate table with 2 columns - Cost Center and if that cost center is a core or embed.  I have these 2 tables set up with a relationship but I haven't been able to take advantage of this yet in my report.

For now, I added another column to the original data table listing if the employee is a core or embed and I'm trying to create a calculated column with 'Team'+'Core or Embed' to use in the report but I'm getting an expression error because I can't use a + operator.

The end result I'm looking for is a stacked column chart that shows how many members of a team are core and how many are embeds.

Thanks for your help so far and thanks in advance if you have any other tips!

[v-sihou-msft]

@asavadians

In this scenario, I suggest you create two calculated tables with SUMMARIZE() functions. One is for Core, the other is for Embedded. Then you can build the relatonship between these two tables based on Team column. There will be two different count for each Team. Just put the two "count" fields into your stacked column chart.

Regards,

[asavadians]

Hi Simon,

Thanks for that tip.  I created a table using the summarize function as you recommended.  Below is the expression used.  'Option 3 - By Team' is the primary data table that I'm using. the 'Embed or Core' column lists whether the employee is considered Core or Embed.  The below expression returns back the total # of employees per team and isn't giving me the # of Core.  Can you spot where I messed up?

Count of Core = SUMMARIZE('Option 3 - By Team', 'Option 3 - By Team'[Team], "# of Core per Team", COUNTAX('Option 3 - By Team', 'Option 3 - By Team'[Embed or Core]="Core"))

[v-sihou-msft]

@asavadians

You should limit the context in your calculation, please refer to formula below:

Count of Core = SUMMARIZE('Option 3 - By Team', 'Option 3 - By Team'[Team], 
"# of Core per Team", 
CALCULATE(COUNTAX('Option 3 - By Team'), 
FILTER('Option 3 - By Team','Option 3 - By Team'[Embed or Core]="Core"))
)

Regards,

Simon Hou