Count Maximum Code based on Key in Summarized Table

[AMCMILLAN]

Having a nightmare today, mind has gone blank (spending too much time on the one dashboard)
I have a summarized table (code below) which brings back the following table when created.
As you can see MET5.01 and MET3.73 have 2 values. What I would like is to only show the higher value, ie maximum based on the KPI_KEY.....suggestions would be good thanks.

SummarizedTable =

SUMMARIZE(

    F_BALANCED_SCORECARD_METRICS,

    F_BALANCED_SCORECARD_METRICS[TIMEFRAME],

    F_BALANCED_SCORECARD_METRICS[COST_CENTRE_NUMBER],

    F_BALANCED_SCORECARD_METRICS[LEVEL],

    F_BALANCED_SCORECARD_METRICS[PERIOD_KEY],

    F_BALANCED_SCORECARD_METRICS[KPI_KEY],

    F_BALANCED_SCORECARD_METRICS[BRAG_COLOUR_F],

    F_BALANCED_SCORECARD_METRICS[BRAG_CODE])

[v-sshirivolu]

Hi @AMCMILLAN ,

I've created a sample .pbix file showing how to get the maximum VALUE per KPI per cost centre and also how to pull related info like Period_key.

The file has Sample data with multiple rows per KPI.

Measures for Max VALUE and related details.

Please find the below attached .pbix file

[v-sshirivolu · Accepted solution]

Hi @AMCMILLAN ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[amitchandak]

@AMCMILLAN , if you  want max of measure you need to create measure like 

Maxx(SUMMARIZE(
F_BALANCED_SCORECARD_METRICS,
F_BALANCED_SCORECARD_METRICS[TIMEFRAME],
F_BALANCED_SCORECARD_METRICS[COST_CENTRE_NUMBER],
F_BALANCED_SCORECARD_METRICS[LEVEL],
F_BALANCED_SCORECARD_METRICS[PERIOD_KEY],
F_BALANCED_SCORECARD_METRICS[KPI_KEY],
F_BALANCED_SCORECARD_METRICS[BRAG_COLOUR_F],
F_BALANCED_SCORECARD_METRICS[BRAG_CODE]) , [Your Measure]) 

if you need What is the category for max value you can use Index function 

example add more column as per need 

maxx(INDEX(1, ALLSELECTED('Item'[Brand]), ORDERBY([Gross], DESC) ), [Brand]) 

Top 2 = CALCULATE([Gross], KEEPFILTERS(TOPN(2,ALLSELECTED('Item'[Brand])
, [Gross],DESC)))

TOP = CALCULATE([Gross], KEEPFILTERS(INDEX(1, ALLSELECTED('Item'[Brand]),
ORDERBY([Gross], DESC))))

Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U