SUM of values grouped by hour

[jaeshin2]

Hello,

I have data with multiple same values. I need to do SUM of all the values based on DISTICT value of another column.

my daya look like this: 

Date	Hour	Hour A	Floor Code	Sensor#	Boardroom Capacity	Sq Ft	Department	Place	Average Zone Count
1/8/2020 0:00	9	9 AM	48th_floor	r31036	14	453	Boardroom	area36	16
1/8/2020 0:00	10	10 AM	48th_floor	r31036	14	453	Boardroom	area36	14
1/8/2020 0:00	10	10 AM	48th_floor	r31036	14	453	Boardroom	area36	14
1/8/2020 0:00	10	10 AM	48th_floor	r31036	14	453	Boardroom	area36	14
1/8/2020 0:00	9	9 AM	48th_floor	r31036	14	453	Boardroom	area36	16
1/8/2020 0:00	9	9 AM	48th_floor	r31036	14	453	Boardroom	area36	16
1/8/2020 0:00	10	10 AM	48th_floor	r31036	14	453	Boardroom	area36	14
1/8/2020 0:00	9	9 AM	48th_floor	r31036	14	453	Boardroom	area36	16
1/8/2020 0:00	9	9 AM	48th_floor	r31036	14	453	Boardroom	area36	16
1/8/2020 0:00	11	11 AM	48th_floor	r31036	14	453	Boardroom	area36	15
1/8/2020 0:00	11	11 AM	48th_floor	r31036	14	453	Boardroom	area36	15
1/8/2020 0:00	11	11 AM	48th_floor	r31036	14	453	Boardroom	area36	15
1/8/2020 0:00	11	11 AM	48th_floor	r31036	14	453	Boardroom	area36	15

As you can at 9 hour for sensor r31036 and for date 1/8/2020, I have 5 rows with same value for Average Zone Count.

so at 9 AM - value is 16

at 10 AM - value is 14

at 11 AM - value is 15

How do I sum all this values so that I get 14 + 15 + 16 = 45 in a measure? 

I guess I need to group by Date, Hour, Sensor# and SUM (Average Zone Count)

Expected measured value: 14 + 15 + 16 = 45

Your help is much appricated! 

[v-lili6-msft · Accepted solution]

hi @jaeshin2 

For your case, just use this measure:

Measure = SUMX(VALUES('Table'[Hour]),CALCULATE(AVERAGE('Table'[Average Zone Count])))

or

Measure 2 = SUMX(SUMMARIZE('Table','Table'[Date],'Table'[Hour]),CALCULATE(AVERAGE('Table'[Average Zone Count])))

Result:

Regards,

Lin

[harshnathani]

Hi @jaeshin2 ,

Measure  = CALCULATE (SUM ('Table'[Average Zone Count]), ALLEXCEPT ( 'Table', 'Table' [Hour]))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[jaeshin2]

This measure gives me very large number. I thinks its summing up all the records? 

I needed to do per date, per hour, per sensor#. 

so I tried  

Measure = CALCULATE (SUM ('ZoneSensors Summary'[Boardroom Average Zone Count]), ALLEXCEPT ('ZoneSensors Summary', 'ZoneSensors Summary'[Date], 'ZoneSensors Summary'[Hour], 'ZoneSensors Summary'[Sensor#]))

Still giving me very large number. 

[harshnathani]

Hi @jaeshin2 ,

Try this

Measure =
CALCULATE (
    SUM ( 'ZoneSensors Summary'[Boardroom Average Zone Count] ),
    FILTER (
        ALL ( 'ZoneSensors Summary' ),
        'ZoneSensors Summary'[Date]
            = MAX ( 'ZoneSensors Summary'[Date] )
            && 'ZoneSensors Summary'[Hour]
                = MAX ( 'ZoneSensors Summary'[Hour] )
            && 'ZoneSensors Summary'[Sensor#]
                = MAX ( 'ZoneSensors Summary'[Sensor#] )
    )
)

Else please share the expected output.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[jaeshin2]

Please see updated questions with some more clarty and expected output.

Thanks