Calculate Highest scorer along with name

[shikhar6339]

Hi All,

I have the below data and I need to create 4 measures:

1) Measure to display who has the highest total points scored in a given year. (to be displayed in card)

2) What was the highest total points scored. (to be displayed in card)

3) What was the average of points scored by that person (to be put in matrix)

4) count of total points scored mored than 100 (to be put in matrix)

Year	Name	Points
2010	ABC	32
2010	XYZ	31
2010	ABC	43
2010	XYZ	53
2010	XYZ	64
2010	ABC	23
2010	RST	54
2010	ABC	76
2010	XYZ	53
2010	RST	23
2011	ABC	76
2011	XYZ	76
2011	ABC	45
2011	RST	35
2011	ABC	76
2011	ABC	43
2011	XYZ	87
2011	RST	97
2011	ABC	67
2011	RST	55
2012	XYZ	86
2012	XYZ	53
2012	ABC	87
2012	ABC	24
2012	ABC	87
2012	XYZ	54
2012	ABC	9
2012	RST	45
2012	RST	52
2012	RST	12

Please let me know if any additional information is required.

[v-cgao-msft · Accepted solution]

Hi @shikhar6339 ,

Please create a new table first.

Table2 = 
SUMMARIZE (
    'Table',
    'Table'[Year],
    'Table'[Name],
    "Total points", SUM ( 'Table'[Points] ),
    "Average of points", AVERAGE ( 'Table'[Points] )
)

Then create these measures.

Who has the highest total score = 
TOPN (
    1,
    ALL ( 'Table2'[Name] ),
    CALCULATE ( MAX ( 'Table2'[total points] ) ), 0
)

The highest total points scored =
CALCULATE ( MAX ( 'Table2'[Total Points] ) )

The average of points =
CALCULATE ( MAX ( 'Table2'[average of points] ) )

Times greater than 100 =
CALCULATE (
    COUNT ( 'Table2'[Total Points] ),
    FILTER ( 'Table2', 'Table2'[Total Points] > 100 )
)

Put these measures into the visual. The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  I cannot understand your last question.

Hope this helps.

[Greg_Deckler]

@shikhar6339 Try:

Highest Points = 
  VAR __Year = MAX('Table'[Year])
RETURN
  MAXX(SUMMARIZE(FILTER('Table',[Year]=__Year),[Year],[Name],"__Points",SUM('Table'[Points])),[__Points])

Highest Person = 
  MAXX(TOPN(1, SUMMARIZE(FILTER('Table',[Year]=__Year),[Year],[Name],"__Points",SUM('Table'[Points])), [__Points]),[Name])

Average = 
  AVERAGEX(FILTER('Table',[Year]=__Year && [Name] = [Highest Person]),[__Points])