Sum of Calculated Measure

[laura_ESNH]

I am managing a program where we are tracking individuals weight loss and the overall success of the group.

I have one table with the participants initial weights:

There is a separate table that contains the monthly weigh-in information.

I created three calculated measures to track the individuals weight loss progress:

Starting Weight = CALCULATE(MAX('Participants'[Starting Weight]))

Current Weight = CALCULATE(MAX('Monthly Weigh-in'[Weight]),FILTER(ALL('Monthly Weigh-in'[Date].[Date]), 'Monthly Weigh-in'[Date].[Date]= MAX('Monthly Weigh-in'[Date])))

Weight Loss = 'Monthly Weigh-in'[Current Weight] - 'Monthly Weigh-in'[Starting Weight]

It resulted in an accurate table of the individual's progress:

 

The issue I’m having is when I try to use the Weight Loss calculated measure to get an overall sum of the group weight loss. I tried adding the Weight Loss measure to a Card visual, but the “Sum” is not accurate. It should be -20.2.

From the research I’ve done, I think I’m supposed to use the SUMX function. I created this measure:

Overall Weight Loss = SUMX(DISTINCT('Monthly Weigh-in'[Initials]), 'Monthly Weigh-in'[Weight Loss])

I also replaced “DISTINCT” with “VALUES”, but neither yielded the correct results.

Based on everything I’ve read, I think I’m on the correct path. I just need some help getting this figured out.

[amitchandak · Accepted solution]

@laura_ESNH , Try a measure like

sumx(values('Participants'[initials]), 'Monthly Weigh-in'[Current Weight] - 'Monthly Weigh-in'[Starting Weight])

[laura_ESNH]

I just needed to use the table that only had the participant listed once. I was so close, thanks for bringing this across the finish line.