Forum Discussion
Summarize Table by Fields and Implementing Calculations
Hello everyone,
I am currently working to figure out the best way to combine and summarize some time series data I have. We are currently tracking quiz scores and would like to eventually see an indicator that shows how the average quiz scores have changed from the previous month for each course. What I currently am trying to figure out is how I can organize the data in a way to show the current average quiz score for each course and the previous months quiz score. I have a list of all the quizzes taken with the course name, the percent correct, and the start and end time of the quiz.
The data currently looks something like this:
Each instance in the table is a quiz taken.
What I would like to do is to create a new table that summarizes the results to look something like this for each course:
And so on for each course.
Steps to take:
- Group by the course name
- Find the current avg quiz score for that course
- Find the previous months average quiz score for that course
- Add a calculated column = (cur month avg – pre month avg) / pre month avg
Any advice on how to accomplish these first three steps would be greatly appreciated, thanks!
Hi arhomberg ,
Sorry about my mistake. DATEADD needs to put the date column into the visual to work.
I made a simple example. I used the simplest function to filter this month and last month. Please check the screenshots and attachments.
Current Avg = CALCULATE(AVERAGE('Table'[Percent_Correct]),FILTER('Calendar',YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY())))Prev Month Avg = CALCULATE(AVERAGE('Table'[Percent_Correct]),FILTER('Calendar',YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY())-1))Percent change = DIVIDE([Current Avg]-[Prev Month Avg],[Prev Month Avg])Hope to help you. Sorry again.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi arhomberg
Where is your previous month data, in the same table? You can add a Month column, then group by course name and month, so you can get the avg for each month
Hello Anonymous
Thanks for the advice! Since the data is a list of all the quiz scores, the 'previous month' data is all in the same table, it would just be found by taking the current average for -30 days (or -1 month). I could do as you suggest and create a month column, group by the course name and month to find teh current averages, but would there be a way then add the calculation for the average of the previous month as well?
Hi arhomberg ,
Solving with DAX will be relatively simple.
1.Create a calendar table, and create relationship between it and the fact table
2.Current Avg=CALCULATE(AVERAGE(Table[Percent_Correct]),DATEADD('Calendar'[Date],0,MONTH))
Prev Month Avg=CALCULATE(AVERAGE(Table[Percent_Correct]),DATEADD('Calendar'[Date],-1,MONTH))
Percent change=DIVIDE([Current Avg]-[Prev Month Avg],[Prev Month Avg])
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Thanks so much for this!
A quick question to clarify, what exactly should I be putting into the Calendar table? Would this be all the dates which the quizes exist, or just the current date? Sorry for the ignorance, but I am new to DAX. Can I also assume that the formulas you provided above are measures?
Thanks again!
Hi arhomberg ,
You can fill in the date manually, or you can refer to a date that exists in an existing table.
You can create a calendar table like
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))Or
Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))DAX: CALENDAR function
Also there‘s a function called CALENDARAUTO you can use.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
