Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andreaaizza
New Member

Average count per month per id

Hi there, 

 

have some measurement like below: 

iddatemeasure
id11.6.20171010
id210.7.20171016
id21.6.201710
id210.7.201720
id215.7.201726


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:

idmonthcount_per_month
id1Jun.171
id1Jul.171
id2Jun.171
id2Jul.172

 

Desired result: average count per month: 

monthave_count_per_month
Jun.171
Jul.171,5

 

Cheers, 

Andrea

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@andreaaizza

 

Hi, you can obtain the desired result in this way:

 

Step 1: In Query Editor

 

Parse the Date and Create the column Month-Year

 

Column-Date.gif

 

Step 2:

 

Create a Measure:

 

AVG_Count_Per_month =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[month-year],
        Table1[id],
        "Count", COUNT ( Table1[id] )
    ),
    [Count]
)

Ready

 

Avg.png




Lima - Peru

View solution in original post

8 REPLIES 8
Vvelarde
Community Champion
Community Champion

@andreaaizza

 

Hi, you can obtain the desired result in this way:

 

Step 1: In Query Editor

 

Parse the Date and Create the column Month-Year

 

Column-Date.gif

 

Step 2:

 

Create a Measure:

 

AVG_Count_Per_month =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[month-year],
        Table1[id],
        "Count", COUNT ( Table1[id] )
    ),
    [Count]
)

Ready

 

Avg.png




Lima - Peru

@Vvelarde Thanks, this solved perfectly! 

dilumd
Solution Supplier
Solution Supplier

Hi,

 

Please use below function,

m_MonthlyAvg = DIVIDE(SUMX('Count',[m_Count]),COUNTA('Count'[Count]))

 

Capture.JPG

Hi @dilumd

Thanks and sorry for being dummy: how do you create m_Count?

Regards,

Andrea

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: 

iddatemeasure
id11.6.20171010
id210.7.20171016
id21.6.201710
id210.7.201720
id215.7.201726

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

1.png

 

add a new column as shown below to Concatenate both column together.

2.JPG

 

and then i'll make the type of the column "text".

 

3.JPG

 

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,

5.JPG

 

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

 

Hi @dilumd, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.