Overall Percent Complete

[Reino66]

I have a data set that roughly looks like this:

State	Date	Percent Complete
CT	1/1/2024	25
CT	1/8/2024	40
MA	1/1/2024	50
RI	1/1/2024	10
RI	1/8/2024	30
RI	1/15/2024	70
NY	1/1/2024	100
NJ	1/8/2024	20
NJ	1/15/2024	50
DE	1/15/2024	75

It is on a sharepoint list being filled via a MS Form.  The states don't always make a report so I need to be able to calculate the overall percent complete of the project based on their highest reported percentage.  The calculation I have now seems to be adding them together (for example CT is added to 65 instead of just using the highest reported 40).  I thought about using latest date, but some states stop reporting once they hit 100.  Thank you in advance.

[lbendlin · Accepted solution]

Group by State, then aggregate by MAX Percent Complete.  (unless you have scenarios where they modify the percentage down too)

[Anonymous · Accepted solution]

Thanks for the reply from @lbendlin , please allow me to provide another insight:

Hi @Reino66 ,

You can try formula like below to create measure:

Overall Percent Complete = 
VAR MaxPercentByState =
    SUMMARIZE(
        'Table',
        'Table'[State],
        "MaxPercent", MAX('Table'[Percent Complete])
    )
RETURN
    AVERAGEX(MaxPercentByState, [MaxPercent])

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Group by State, then aggregate by MAX Percent Complete.  (unless you have scenarios where they modify the percentage down too)