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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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