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

Right now the largest table has 66,000 rows. I highlighted the client ID on the two tables to merge and it just kept saying estimating matches and wouldn't let me click OK. I'm guessing it's too big.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

reast,

Could you please share sample data of the above tables and post expected result in table format? Also please help to post a snapshot about relationships among these tables.

Regards,
Lydia

  • reast's avatar
    reast
    Helper II
    8 years ago

    I was able to get the two screening tools into the same table and calculate the level of care. I was able to determine an average level of care over the past 6 months using a measure. However, I want to use that average level of care per client to indicate clinician availability. A clinician with 6 clients at a high level of care has less room than a clinician with 6 clients at a lower level of care. So I need the level of care average to be fixed to the client. However, since it's a measure, it calculates in the moment, and works to show in a table on a dashboard, but I can't use it to make further calculations (i.e. totaling a clinicians weighted caseload). Should I post this as a new issue since it's different now?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      reast,

      Please share sample data of your tables and post new expected result here.

      Regards,
      Lydia

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