Forum Discussion

reast's avatar
reast
Helper II
8 years ago
Solved

Calculating from Columns in Different Tables

We have two screening tools and together, they are used to determine a client's level of care. Screening A is just one question with one score. Screening B has multiple questions and the sum, as well...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi reast

     

    I think I see what you needed.  I think this calculated table is close

     

    New Calcuated Table = 
VAR Group1 = SUMMARIZECOLUMNS('Screening'[Client ID] , "Average Weight" , AVERAGE('Screening'[LOC]))
VAR Group2 = SUMMARIZECOLUMNS('Screening'[Client ID],'Screening'[Clinician])
VAR Group3 = GROUPBY(
                        NATURALINNERJOIN(Group1,Group2) ,
                            'Screening'[Clinician],"Weighted Caseload",
                            SUMX(CURRENTGROUP(),
                                [Average Weight]
                                )
                         )
RETURN Group3

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi reast

 

I think I see what you needed.  I think this calculated table is close

 

New Calcuated Table = 
VAR Group1 = SUMMARIZECOLUMNS('Screening'[Client ID] , "Average Weight" , AVERAGE('Screening'[LOC]))
VAR Group2 = SUMMARIZECOLUMNS('Screening'[Client ID],'Screening'[Clinician])
VAR Group3 = GROUPBY(
                        NATURALINNERJOIN(Group1,Group2) ,
                            'Screening'[Clinician],"Weighted Caseload",
                            SUMX(CURRENTGROUP(),
                                [Average Weight]
                                )
                         )
RETURN Group3

reast's avatar
reast
Helper II
8 years ago

That's perfect!! Thank you so much! Happy Thanksgiving!!