Forum Discussion
Max Annual Percentage Change
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
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.