Forum Discussion
Summarize grouping by 2 columns
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: c...
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]))
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