Forum Discussion
Solved
Maximum from calculated Sum
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
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
1 Reply
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