Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table that looks like this:
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?
Solved! Go to Solution.
Hi @Anonymous ,
Create a matrix,and you will see:
For the related .pbix file,pls see attached.
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
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
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 |
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |