Summarize grouping by 2 columns

[Anonymous]

Hallo,

I need to group the table 

1) a column: grouped by date

2) second column: grouped by hours

3) 3d coumn: count distinct for each hour how many operators I have for each hour

4) 4 column: count nr CONTENITORI for each hour

Here an example of the table:

MODULO	MENU	CONTENITORE	INIZIO	MATRICOLA
Replenish	.Reappro palette	436000000011889745	10 ago 20 06:05:40	SHER
Replenish	.Reappro palette	436000000011785597	10 ago 20 06:07:27	SEIB
Replenish	.Reappro palette	436000000010710972	10 ago 20 06:08:51	SHER
Replenish	.Reappro palette	436000000011920455	10 ago 20 06:11:07	SEIB
Replenish	.Reappro palette	436000000011843730	10 ago 20 06:11:33	EMAT
Replenish	.Reappro palette	436000000011867972	10 ago 20 06:12:35	SHER
Replenish	.Rgt pkg manuel	436000000011815690	10 ago 20 06:13:54	SINN
Replenish	.Reappro palette	436000000007795449	10 ago 20 07:15:16	SEIB
Replenish	.Rgt pkg manuel	436000000011329012	10 ago 20 07:14:06	ASTL
Replenish	.Reappro palette	436000000011749926	10 ago 20 07:15:23	EMAT
Replenish	.Reappro palette	436000000011220098	10 ago 20 07:17:28	SEIB
Replenish	.Reappro palette	436000000011923883	10 ago 20 07:20:01	SHER
Replenish	.Reappro palette	436000000011918308	10 ago 20 07:19:46	EMAT
Replenish	.Reappro palette	436000000011792830	10 ago 20 07:21:35	SEIB
Replenish	.Reappro palette	436000000011915222	10 ago 20 07:22:27	ASTL
Replenish	.Reappro palette	436000000011500732	10 ago 20 07:23:42	EMAT
Replenish	.Rgt pkg manuel	436000000011524585	10 ago 20 07:24:00	EBOJ
Replenish	.Reappro palette	436000000010925635	10 ago 20 07:27:03	SEIB
Replenish	.Rgt pkg manuel	436000000011663017	10 ago 20 07:28:24	EBOJ
Replenish	.Rgt pkg manuel	436000000011844041	10 ago 20 07:20:00	MAMI
Replenish	.Rgt pkg manuel	436000000011804472	10 ago 20 06:32:51	DEKA
Replenish	.Reappro palette	436000000011841484	10 ago 20 06:36:19	ASTL
Replenish	.Reappro palette	436000000010720605	10 ago 20 06:39:43	SEIB
Replenish	.Rgt pkg manuel	436000000011024337	11 ago 20 06:30:02	EBOJ
Replenish	.Reappro palette	436000000011899997	11 ago 20 06:44:18	SEIB
Replenish	.Reappro palette	436000000011483202	11 ago 20 06:41:31	ASTL
Replenish	.Reappro palette	436000000010064051	11 ago 20 06:45:07	EMAT
Replenish	.Rgt pkg manuel	436000000011810305	11 ago 20 06:46:14	DEKA
Replenish	.Reappro palette	436000000011548604	11 ago 20 06:46:35	SHER
Replenish	.Rgt pkg manuel	436000000011143458	11 ago 20 06:43:20	EBOJ
Replenish	.Rgt pkg manuel	436000000011683084	11 ago 20 06:50:13	EBOJ
Replenish	.Reappro palette	436000000011843662	11 ago 20 06:52:53	EMAT
Replenish	.Rgt pkg manuel	436000000011783333	11 ago 20 06:59:53	ABMA
Replenish	.Reappro palette	436000000010683931	11 ago 20 06:50:00	SHER
Replenish	.Reappro palette	436000000011662225	11 ago 20 06:59:11	SEIB
Replenish	.Reappro palette	436000000008626728	11 ago 20 06:58:13	EMAT
Replenish	.Rgt pkg manuel	436000000011914621	11 ago 20 06:59:55	EBOJ
Replenish	.Rgt pkg manuel	436000000011792557	11 ago 20 06:47:21	DEKA
Replenish	.Reappro palette	436000000008960372	11 ago 20 07:04:04	SEIB
Replenish	.Rgt pkg manuel	436000000011777035	11 ago 20 07:04:21	EBOJ
Replenish	.Rgt pkg manuel	436000000011909764	11 ago 20 07:05:17	DEKA
Replenish	.Rgt pkg manuel	436000000011442971	11 ago 20 07:13:18	EBOJ
Replenish	.Reappro palette	436000000011400698	11 ago 20 07:15:22	ASTL
Replenish	.Reappro palette	436000000011873485	11 ago 20 07:12:55	SEIB

Here the output I'm looking for to get:

date	hour	nr op	nr  contenitori
10/08/2020	6	6	10
10/08/2020	7	6	13
11/08/2020	6	7	18
11/08/2020	7	4	6

Do you have any formulas to solve it?

[v-yiruan-msft]

Hi @Anonymous ,

You can create below 2 calculated columns and a calculated table to achieve it:

1. Create calculated columns to get the date and hour

Date = DATE(YEAR('Table'[INIZIO]),MONTH('Table'[INIZIO]),DAY('Table'[INIZIO]))

Hour = HOUR('Table'[INIZIO])

2. Create a summary table

Summary = 
SUMMARIZE (
    'Table',
    'Table'[Date],
    'Table'[Hour],
    "Nr op", CALCULATE (
        DISTINCTCOUNT ( 'Table'[MATRICOLA] ),
        FILTER (
            ( 'Table' ),
            DATE ( YEAR ( 'Table'[INIZIO] ), MONTH ( 'Table'[INIZIO] ), DAY ( 'Table'[INIZIO] ) ) = 'Table'[Date]
                && HOUR ( 'Table'[INIZIO] ) = 'Table'[Hour]
        )
    ),
    "Nr contenitori", CALCULATE (
        COUNT ( 'Table'[CONTENITORE] ),
        FILTER (
            ( 'Table' ),
            DATE ( YEAR ( 'Table'[INIZIO] ), MONTH ( 'Table'[INIZIO] ), DAY ( 'Table'[INIZIO] ) ) = 'Table'[Date]
                && HOUR ( 'Table'[INIZIO] ) = 'Table'[Hour]
        )
    )
)

Best Regards

Rena

[amitchandak · Accepted solution]

@Anonymous , Try like this

New column 

Date =Table[INIZIO].date
hour =hour(Table[INIZIO])

New Table

summarize(Table, Table[Date], Table[hour], "op nr", distinctcount(Table[MATRICOLA]), "contenitori",count(Table[CONTENITORE]))