Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

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 li...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.