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

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

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

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.

         

         

  • 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.