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
Anonymous
Not applicable

Aggregate daily data into total per month and then get monthly average

I have a table that looks like this:

alejandrogdp_0-1597534854968.png

 

I would like to perform the following aggregations on my data:

1) convert daily data to monthly by summing "number" column grouping by month, region, zone, and subzone
2) get a per month average based on the results in step 1. E.g. if I have monthly totals for region1>zone1>subzone1, I would like to sum the monthly totals and divide by the number of months - i.e. monthly total average.

 

How would you solve this using a DAX metric?

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Create a matrix,and you will see:

Annotation 2020-08-17 110959.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Create a matrix,and you will see:

Annotation 2020-08-17 110959.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
AllisonKennedy
Super User
Super User

@Anonymous
You don't need to use a DAX measure for this, you can do this using the built in Matrix visual in Power BI.

I do agree with @Ibendlin that you should have a DimDate table.

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Once you get that into your data model, you simply need to put in a Matrix visual with:
Table[Number] in Values with SUM aggregation
Table[Number] in Values with AVERAGE aggregation
Note you can put the same column into Values twice, and click the arrow next to the column name to change from SUM to AVERAGE the second time.

Then put DimDate[Month] in Columns
Table[Region] in Rows

You can add Zone and Subzone into Rows under Region, and use the Drill Down arrows (usually at top right of visual) to drill to the level of detail desired.

I hope that helps!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

pranit828
Community Champion
Community Champion

HI @Anonymous 

 

Use the below formula

 

AverageCustomerSpend =
    AVERAGEX(
        SUMMARIZE(Table,
            Table[region], Table[zone], Table[subzone], Year(Table[date]), Month(Table[date])
            "Number sum", SUM(Table[Number])),
        [Number Sum])

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
lbendlin
Super User
Super User

You would use a calendar/dates table, link it to your fact table, and let the data model do the work for you by choosing the date column(s) from the calendar table instead of the fact table.

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.