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

reast's avatar
reast
Helper II
8 years ago

Screening:

Date                Client ID              LOC     Clinician

4-1-17             1234                   2                A

5-5-17             1234                   3                A

6-4-17             7890                   4               A

4-6-17             7890                   5               A

5-8-17             5678                   1               B

9-3-17             5678                   3               B

 

Table using LOC Average Measure on Dashboard shows:

1234              2.5

7890              4.5

5678               2

 

What I want to do then is use the average LOC per client and then add them together to get a weighted caseload for each clinician.

Clinician A        7

Clinician B        2

 

Thanks for your help!

 

 

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!!