Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
asavadians
Frequent Visitor

Creating a Visual based on data from another Visual

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:

 

 

HR5
Sales10
Careers17
Careers Mobile22
Commerce59

 

HR10
Sales12
Careers40
Careers Mobile4
Commerce19

 

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?

5 REPLIES 5
BhaveshPatel
Community Champion
Community Champion

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. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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!

@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,

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"))

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.