cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
aashton
Helper V
Helper V

Combining values from 2 tables in Stacked Bar

Hello,

I have Table A set up as follows:

aashton_0-1675797399037.png

I then have a separate Table B for just one title, Locums, because it is pulling from a separate data source. (Linked on Location) This is set up a little differently:

aashton_0-1675797924554.png

 

I have a measure Total FTE, which is basically TableA.TotalFTE + TableB.TotalFTE.  But, I'd like to break down the Total FTES by title in various visuals.  I'm getting confused on how to do this, as TAble A is Regular MD and Regular CRNA, and Table B is Locum MD and Locum MD.  For instance, I'd like to put it on a stacked bar chart by Location to show the % of each title.

6 REPLIES 6
v-xinruzhu-msft
Community Support
Community Support

Hi @aashton 

You can create a new table.

 

Table 2 = SUMMARIZE(TableA,[Title])

 

Then  create a measure in tableA

 

Measure = SWITCH(TRUE(),MAX('Table 2'[Title])="MD",CALCULATE(SUM(TableA[FTE])+SUM(TableB[MD FTE]),TableA[Title]="MD",TableB[Location]=MAX(TableA[Location])),MAX('Table 2'[Title])="CRNA",CALCULATE(SUM(TableA[FTE])+SUM(TableB[CRNA FTE]),TableA[Title]="CRNA",TableB[Location]=MAX(TableA[Location])),SUM(TableA[FTE])+SUM(TableB[MD FTE])+SUM(TableB[CRNA FTE]))

 

Put the "location" field in table a to x-axis and "title" field in table 2 to legnd, and put the measure to y-axis.

vxinruzhumsft_0-1675822240452.png

Best Regards!

Yolo Zhu

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

I changed the format of Table B, I thought it might be easier if set-up like this:

 

aashton_0-1675874851505.png

 

I then wanted to create a column with an If statement, IF(TableA.Title = "MD", "Regular MD", IF(TableB.Title = "Locum MD", "Locum MD"....etc...But it's not letting me pick fields from different tables

Hi @aashton 

Based on the change of table B, you can refer to the following example.

Create two tables

 

Table = UNION(SUMMARIZE(TableA,[Title]),SUMMARIZE(TableB,[Title]))

Table 2 = var a=VALUES(TableB[Location])
var b=SUMMARIZE(FILTER(TableA,[Location] in a=FALSE()),[Location])
return UNION(a,b)

 

Then create a measure 

 

Measure = CALCULATE(SUM(TableA[FTE])+SUM(TableB[FTE]),TableA[Location]=MAX('Table 2'[Location]),TableB[Location]=MAX('Table 2'[Location]),TableA[Title]=MAX('Table'[Title]),TableB[Title]=MAX('Table'[Title]))

 

Put the location field of table 2  to x-axis, put the title of  tale to legend, put the measure to y-axis

vxinruzhumsft_0-1675928042176.png

Best Regards!

Yolo Zhu

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

 

 

 

Hi @v-xinruzhu-msft ...thanks for your help!  But the titles in Table A and and TAble B should be different, not combined.  Table A are REgular MD and CRNA and Table B are Locum MD and CRNA.  And there are more titles than just MD and CRNA, I just used those examples.

Thanks but I am getting the same totals for each location:

Table = UNION(SUMMARIZE('CR HCM Monthly Data', 'CR HCM Monthly Data'[Complex Title]), SUMMARIZE('Ringo Locum Leased','Ringo Locum Leased'[Title]))
 
Table 2 =
var a = VALUES('Ringo Locum Leased'[Biz_Structure Region])
var b = SUMMARIZE(FILTER('CR HCM Monthly Data', 'CR HCM Monthly Data'[Worker Region] in a=FALSE()),'CR HCM Monthly Data'[Worker Region])
return UNION(a,b)
 
Title = CALCULATE(SUM('CR HCM Monthly Data'[FTE Status])+'Ringo Locum Leased'[Total Ringo FTE],'CR HCM Monthly Data'[Worker Region]=MAX('Table 2'[Biz_Structure Region]),'Ringo Locum Leased'[Biz_Structure Region]=MAX('Table 2'[Biz_Structure Region]),'CR HCM Monthly Data'[Complex Title]=MAX('Table'[Complex Title]),'Ringo Locum Leased'[Title]=MAX('Table'[Complex Title]))
 
aashton_0-1675964549055.png

 

 

 

Hi @aashton 

The "Title"measure you have provided should be 

Title = CALCULATE(SUM('CR HCM Monthly Data'[FTE Status])+SUM('Ringo Locum Leased'[Total Ringo FTE]),'CR HCM Monthly Data'[Worker Region]=MAX('Table 2'[Biz_Structure Region]),'Ringo Locum Leased'[Biz_Structure Region]=MAX('Table 2'[Biz_Structure Region]),'CR HCM Monthly Data'[Complex Title]=MAX('Table'[Complex Title]),'Ringo Locum Leased'[Title]=MAX('Table'[Complex Title]))
 

Best Regards!

Yolo Zhu

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

 

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors