Forum Discussion
Calculating from Columns in Different Tables
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
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?
reast,
Please share sample data of your tables and post new expected result here.
Regards,
Lydia
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!
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 Group3That's perfect!! Thank you so much! Happy Thanksgiving!!