topic Re: Overall Percent Complete in DAX Commands and Tips

Overall Percent Complete

Reino66 — Sun, 12 May 2024 13:45:26 GMT

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.

Re: Overall Percent Complete

lbendlin — Sun, 12 May 2024 22:01:43 GMT

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

Re: Overall Percent Complete

Anonymous — Thu, 16 May 2024 07:22:30 GMT

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.