Max Annual Percentage Change

[Anonymous]

Dear all,

I have a Sharepoint list that looks like the attached (with lots more quarter dates and lots more cities).

I'm creating a dashboard that analyses these numbers. The user only gets to select their quarter.

How can I create a formula that returns the best performing city defined as having the highest annual percentage increase in price in that selected quarter?

Any advice greatly welcomed.

Many thanks

Katie

[Anonymous]

I really appreciate the response - I had to solve the problem yesterday so managed to do it a different (less elegant!) way but will mark yours as a solution as it certainly looks like it's done the trick!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I think you can try this code to create a measure to calculate the best performance city.

Best Performing City =
VAR _SELECTQUARTER =
    SELECTEDVALUE ( DimQuarter[Quarter] )
VAR _QUARTERLASTYEAR =
    EOMONTH ( _SELECTQUARTER, -12 )
VAR _SUMMAIRZE =
    SUMMARIZE (
        'Table',
        'Table'[City],
        "Change",
            VAR _CUR =
                CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Quarter] = _SELECTQUARTER )
            VAR _PRE =
                CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Quarter] = _QUARTERLASTYEAR )
            RETURN
                DIVIDE ( _CUR - _PRE, _PRE )
    )
RETURN
    MAXX ( FILTER ( _SUMMAIRZE, [Change] = MAXX ( _SUMMAIRZE, [Change] ) ), [City] )

Result is as below.

Best Regards,
Rico Zhou

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