Maximum from calculated Sum

[prateekraina]

Hi Guys,

Following is my dataset:

Camera	TimeStamp	Count
Cam 1	10:00	1
Cam 1	10:00	1
Cam 1	10:00	1
Cam 2	10:00	1
Cam 2	10:00	1

I have written DAX to calculate the sum grouped by camera:

Total Count= SUM(Table[Count])

And i get following result in a table:

Camera	Total Count
Cam 1	3
Cam 2	2

Problem Statement:

The issue which i am facing is to get the camera which has highest count associated to it. In this case, it is Cam 1.

I have tried below DAX expression to get the Camera but it is not yielding the desired result. 

CALCULATE(VALUES(Table[Camera]),FILTER(Table,Table[Count] = MAX(Table[Count])))

Kindly help.

Prateek Raina

[prateekraina · Accepted solution]

I have achived the desired result with below DAX:

Camera With Max People = 
VAR MaximumNoOfCountByCamera = MAXX(VALUES('Table'[Camera]),[Total Count])
VAR Table_SumOfCountGroupByCamera = SUMMARIZECOLUMNS(Table[Camera],"Count",SUM(Table[Count]))
RETURN
CALCULATE(VALUES(Table[Camera]),FILTER(Table_SumOfCountGroupByCamera,[CountofPeople] = MaximumNoOfCountByCamera))

If anybody finds any other alternative, let me know 🙂

Prateek Raina