COUNTDISTINCT VALUE FOR EACH HOUR, FOR EACH DAY

[Anonymous]

Hi all

I've got a table:

https://www.dropbox.com/s/7yawtwvpsjexnx1/FILE_EX.xlsx?dl=0

From this, I need of table as structured as:

month

day

hour(HH)

nr CONT--> nr of CONT for each hour, for each day

nr ops--> count distinct for each hour of the MATRICOLAs, for each day

here an example of the output reached:

month	day	hh	nr cont	nr ops
9	1	6	240	13
9	1	7	351	13
9	1	8	302	17

Do you have any solution?

Thanks for your help

[v-jingzhang · Accepted solution]

Hi @Anonymous ,

You can first add new columns:

Month = MONTH(Foglio1[DATA])
Day = DAY(Foglio1[DATA])
HH = HOUR(Foglio1[DATA])

And then create a new table:

Table =
SUMMARIZE (
    Foglio1,
    Foglio1[Month],
    Foglio1[Day],
    Foglio1[HH],
    "nr CONT", COUNT ( Foglio1[cont] ),
    "nr ops", DISTINCTCOUNT ( Foglio1[MATRICOLA] )
)

Is this what you want?

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous ,

Try something like this

sumx(summzarize(Table, Table[month], Table[day], Table[Hour], "_1", distinctcount(Table[Ops])),[_1])

Change as per need. I did not check the file yet