Forum Discussion

Reino66's avatar
Reino66
New Member
2 years ago
Solved

Overall Percent Complete

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's avatar
    lbendlin
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.