cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Combining values from 2 tables in Stacked Bar

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.

6 REPLIES 6
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.

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.

Helper V

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

Community Support

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.

Helper V

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.

Helper V

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

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors