SUM of grouped values when there is one unique value in each row.

[jaeshin2]

How would i get SUM of grouped values when there is one unique value in each row.

For below example - 

I need to group by Area and get SUM of Capacity.

Result will be = 12 (Area 4, 5, 6 has value 4)

I have tried mesaure like this: 

SUMX(SUMMARIZE('NonBoardRoomOfficeData', NonBoardRoomOfficeData[Place], NonBoardRoomOfficeData[Department], NonBoardRoomOfficeData[Area)], NonBoardRoomOfficeData[Floor]),CALCULATE(Sum(NonBoardRoomOfficeData[Capacity])))

This gives me total of 10 rows which is wrong. I am assuming its because each row has unique 'Names'

Your help is appriciated! 

Location Type	Names	Place	Area	Floor	FloorCode	Department	Capacity
Work Station	HR	area6	6	48	48th_floor	Human Resources	4
Work Station	Lucy Gruber	area5	5	48	48th_floor	Human Resources	4
Work Station	Stephanie Giacomini	area5	5	48	48th_floor	Human Resources	4
Work Station	Susan Oliver	area5	5	48	48th_floor	Human Resources	4
Work Station	Will Guan	area5	5	48	48th_floor	Human Resources	4
Work Station	Rosanna Katrick	area4	4	48	48th_floor	Human Resources	4
Work Station	Dax Sardinha	area4	4	48	48th_floor	Human Resources	4
Work Station	EA	area6	6	48	48th_floor	Human Resources	4
Work Station	Steve Johnson	area4	4	48	48th_floor	Human Resources	4
Work Station	Winnie Ma	area4	4	48	48th_floor	Human Resources	4

[FrankAT · Accepted solution]

Hi @jaeshin2,

it's not right clear what you want to achieve. Here is my solution as far as I have understood you correctly.

 Regards FrankAT

[dedelman_clng]

Based on what you asked (your expected results don't seem to match the question), you need to bring the value you are summing inside the SUMMARIZE, or SUMX won't know what to look at:

Total Capacity = 
SUMX (
    SUMMARIZE (
        'NonBoardRoomOfficeData',
        NonBoardRoomOfficeData[Place],
        NonBoardRoomOfficeData[Department],
        NonBoardRoomOfficeData[Area],
        NonBoardRoomOfficeData[Floor],
        "Capacity", SUM ( NonBoardRoomOfficeData[Capacity] )), //This is the new line
[Capacity] )

If you really only want to see "4" for each area, and 12 as the total, create the table from Summarize as a calculated table (CalcTable), using MIN instead of SUM, then your measure would be

Total Capacity = SUM( CalcTable[Capacity] )

Hope this helps

David

[parry2k]

@jaeshin2 sorry not fully clear what you are looking for, can you elaborate bit more? You want to sum capacity by area or what? Not sure what you mean by 4,5,6 and value 4