Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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. 😊
Hi @dilumd
ok. Last issue is that I start with data like this:
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 |
and not "Count" (count per month) is there. Should I create new table with that count or what is best solution?
Many thanks in advance,
Andrea
Hi,
** Make sure you have the correct date and time format, if not you have first correct that.
This is the way i did it, (there can be many ways of doing this)
Since you have to calculate monthly average you have to have a column with Month and Year as a text column where you can calculate the count of raws.
Based on your date column i'd add month and a year as shown below in query editor, and then
add a new column as shown below to Concatenate both column together.
and then i'll make the type of the column "text".
and then you can add below two measures,
1. measure of the count
m_Count = SUM('Count'[Count])
2. measure of avg monthly total
m_MonthlyAvg = DIVIDE(SUMX('Count',[m_Count]),COUNTA('Count'[MonthYear]))
please see the final outcome,
please refer the below m query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykwxVNJRMtMz1DMyMDQHMg2VYnVAwkZAtrmeoQG6uDF25SYQ5XBxI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Month", type date}, {"Count", Int64.Type}}), #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Month]), type text), #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Month]), type number), #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Year",{{"Year", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "MonthYear", each [Month Name]&" "&[Year]), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"MonthYear", type text}}) in #"Changed Type3"
You can learn DAX with below youtube channel
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw
Hope this helps.
Dilum
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |