Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
A relative newbie to DAX so take it easy on me if it's a simple solution. So i have several years of data of different info and i want to calculate an average of the monthly total by a certain category as each category may have several entries per month. An example of the data and what i would expect the result to be is as follows:
Hi @Kauto2007 ,
First of all, many thanks to for your very quick and effective replies.
Based on my testing, please try the following methods:
1.Create the simple table.
2.Create the new column to filter the month.
Month = MONTH('Table'[Date])
3.Create the new measure to calculate average for category.
Average =
VAR categ = SELECTEDVALUE('Table'[Category])
VAR total_val = CALCULATE(SUM('Table'[Value]), FILTER(ALLSELECTED('Table'), 'Table'[Category] = categ))
VAR month_num = CALCULATE(DISTINCTCOUNT('Table'[Month]), 'Table'[Category] = categ)
RETURN
CALCULATE(
DIVIDE(total_val, month_num)
)
4.Select Drag the category field into the slicer.
5.Select the Category A. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I know i can just add an average in from the Analystics part of the time series analysis but i need the average to calculate the STD of the averages to create my upper and lower control limits..
@xifeng_L Thanks for that. I tried that measure and although it doesnt come up with any errors so it is accepted, when i add it into a visual it is blank
Hi @Kauto2007 ,
You can try below measure.
average of the monthly total = IF(HASONEFILTER('Table'[Category]),AVERAGEX(ALL('Table'[Month]),CALCULATE(SUM('Table'[Value]))))
Demo - Calculate Average by Month and Category.pbix
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
User | Count |
---|---|
57 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
116 | |
41 | |
40 | |
28 | |
22 |