Summarize Table by Fields and Implementing Calculations

[arhomberg]

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:

1. Group by the course name
2. Find the current avg quiz score for that course
3. Find the previous months average quiz score for that course
4. 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!

[v-stephen-msft]

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.

[arhomberg]

@v-stephen-msft 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!

[v-stephen-msft]

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.

[arhomberg]

@v-stephen-msft Thanks for this! 

I was using the CALENDARAUTO() function, but just wan't to confirm this was what I should have been using. I am experiencing an issue when trying to implement your initial advice from above. When I create the Calendar table, add a relationship to the fact table, and create the measures, the table appears blank. 

When I delete the relationship and let the tables standalone, the table loads the information in the proper format, but the calculation for the previous month is not coming through correctly (even if I trouble shoot and try -6 months, it stays the same).

I am pretty sure I followed your directions and entered the measures correctly:

Any advice on why these calculations might not be working as expected would be wonderful.

Thank you!

[v-stephen-msft · Accepted solution]

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.

[arhomberg]

@v-stephen-msft This is great, thanks for this!

[Vera_33]

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

[arhomberg]

Hello @Vera_33 

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?