Forum Discussion
Average count per month per id
Hi there,
have some measurement like below:
| id | date | measure |
| id1 | 1.6.2017 | 1010 |
| id2 | 10.7.2017 | 1016 |
| id2 | 1.6.2017 | 10 |
| id2 | 10.7.2017 | 20 |
| id2 | 15.7.2017 | 26 |
Trying to calculate an average of counts per month per id, as done below with excel. Can anybody help me to do it with powerbi?
Count per month, per id:
| id | month | count_per_month |
| id1 | Jun.17 | 1 |
| id1 | Jul.17 | 1 |
| id2 | Jun.17 | 1 |
| id2 | Jul.17 | 2 |
Desired result: average count per month:
| month | ave_count_per_month |
| Jun.17 | 1 |
| Jul.17 | 1,5 |
Cheers,
Andrea
Hi, you can obtain the desired result in this way:
Step 1: In Query Editor
Parse the Date and Create the column Month-Year
Step 2:
Create a Measure:
AVG_Count_Per_month = AVERAGEX ( SUMMARIZE ( Table1, Table1[month-year], Table1[id], "Count", COUNT ( Table1[id] ) ), [Count] )Ready
8 Replies
Hi, you can obtain the desired result in this way:
Step 1: In Query Editor
Parse the Date and Create the column Month-Year
Step 2:
Create a Measure:
AVG_Count_Per_month = AVERAGEX ( SUMMARIZE ( Table1, Table1[month-year], Table1[id], "Count", COUNT ( Table1[id] ) ), [Count] )Ready
Vvelarde Thanks, this solved perfectly!
Hi,
Please use below function,
m_MonthlyAvg = DIVIDE(SUMX('Count',[m_Count]),COUNTA('Count'[Count]))Hi
you can just create a new measure using SUM function
=SUM(‘Tablename’[ColumnName])
reason for this is that you can only use measures inside of a expression. 😊
