Combining values from 2 tables in Stacked Bar

[aashton]

Hello,

I have Table A set up as follows:

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:

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.

[v-xinruzhu-msft]

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.

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.

[aashton]

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

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

[v-xinruzhu-msft]

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

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.

[aashton]

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.

[aashton]

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

[v-xinruzhu-msft]

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.