How to get monthly/weekly/daily averages from counting unique IDs

[DANIPAR]

Hello,

I have data table with data (year, month, day, week) and unique #Id. I'm counting unique Id's for year/month/week/day.

Year	Month	Day	WEEK	#Id
2019	January	21	4	00Q4H00000ce0UHUAY
2019	January	21	4	00Q4H00000ce0WDUAY
2019	January	21	4	00Q4H00000ce0XaUAI
2019	January	21	4	00Q4H00000ce1l0UAA
2019	January	22	4	00Q4H00000ce2NbUAI
2019	January	22	4	00Q4H00000ce44CUAQ
2019	January	22	4	00Q4H00000ce4tiUAA
2019	January	23	4	00Q4H00000ce58YUAQ
2019	December	3	49	00Q4H00000gBZRWUA4
2019	December	3	49	00Q4H00000gBZtBUAW
2019	December	3	49	00Q4H00000gBZuYUAW
2019	December	3	49	00Q4H00000gBZxDUAW
2019	December	12	50	00Q4H00000h8jksUAA
2019	December	12	50	00Q4H00000h8jreUAA
2019	December	12	50	00Q4H00000h8kpPUAQ
2019	December	12	50	00Q4H00000h8lrqUAA
2020	February	3	6	00Q4H00000hBHkfUAG
2020	February	3	6	00Q4H00000hBHKLUA4
2020	February	3	6	00Q4H00000hBHLnUAO
2020	February	3	6	00Q4H00000hBHLTUA4
2020	August	21	34	00Q4K000001M37HUAS
2020	August	21	34	00Q4K000001M3LsUAK
2020	August	21	34	00Q4K000001M3LTUA0
2020	August	21	34	00Q4K000001M3U1UAK
2020	August	24	35	00Q4K000001M4ytUAC
2020	August	24	35	00Q4K000001M5jmUAC
2020	August	24	35	00Q4K000001M5kVUAS

My goal is to create a table with average counts of Id's per month/week/day of specified year. Something like this:

Year	Average count of Id's per Month	Average count of Id's per Week	Average count of Id's per Day
2020
2019

[v-robertq-msft]

Hey, @DANIPAR

According to your description, you want to get average id counts per month/week/day of the specified year. You can follow in my footsteps:

1. Create a calculated column:

Month Day = [Month]&"-"&[Day]

2. Create three measures:

Average count of Id's per Month =

var _numberofid=DISTINCTCOUNT('Table'[#Id])

var _numberofmonth=DISTINCTCOUNT('Table'[Month])

return DIVIDE(_numberofid,_numberofmonth)

Average count of Id's per Week =

var _numberofid=DISTINCTCOUNT('Table'[#Id])

var _numberofweek=DISTINCTCOUNT('Table'[WEEK])

return DIVIDE(_numberofid,_numberofweek)

Average count of Id's per Day =

var _numberofid=DISTINCTCOUNT('Table'[#Id])

var _numberofday=DISTINCTCOUNT('Table'[Month Day])

return DIVIDE(_numberofid,_numberofday)

3. Create a Matrix chart and place measures and columns, such as this:

And you can get anything you want.

You can download my test pbix file here

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak · Accepted solution]

@DANIPAR , Have a column like this in your table

Year week to [Year] & [WEEK]
Date: [Day] & "-" &[Month]& "-" &[Year]
Year of the month [Month]& "-" &[Year]

Next, create a measure such as

Week Avg ( divide(count(Table[#Id]), distinctcount(Table[Year Week]))
Split by Brackets (Count(Table[#Id]), distinctcount(Table[Date]))
Split Monthly Average(count(Table[#Id]), distinctcount(Table[Month Year ]))