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
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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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