How to Aggregate date data in Col Series Charts

I have session data where I track the frequency (total times) an objective is accomplished in a single session.

Somedays I have a single 5 hour session.
Somedays I have a 2 sessions( one for 2 hours and one for 3 hours)

In my chart I wanted the track the frequency per day. So on days with multiple session I wanted to aggregate the two values into one value.

For this example we will use the following table called Data

Original Table
ID Date Objective Count
1 1-Sep Clap Hands 4
2 2-Sep Clap Hands 2
3 2-Sep Clap Hands 3
4 3-Sep Clap Hands 5
5 4-Sep Clap Hands 6

Creating the chart with the data as it is will result in Sep 2 appearing on the X-axis twice with the values 2 and 3
I wanted it to appear once with the value 5

Here is how I accomplished this

First I added a virtual column (Total count) with the total count for all row matching this date

sum(Select(Data[Count],and([Date] = [_thisrow].[Date],[Objective] = [_thisrow].[Objective])))

This results in the the table now having this

Table with Virtual Column
ID Date Objective Count Total Count
1 1-Sep Clap Hands 4 4
2 2-Sep Clap Hands 2 5
3 2-Sep Clap Hands 3 5
4 3-Sep Clap Hands 5 5
5 4-Sep Clap Hands 6 6

Next I created a Slice with the following filter

[_ROWNUMBER] = MAX( SELECT( Data[_ROWNUMBER],AND([Date] = [_THISROW].[Date],[Objective] = [_thisRow].[Objective]) ) )

This removed any rows with repeating dates giving me the following table

Filtered Dates
ID Date Objective Count Total Count
1 1-Sep Clap Hands 4 4
3 2-Sep Clap Hands 3 5
4 3-Sep Clap Hands 5 5
5 4-Sep Clap Hands 6 6

After that I created a Chart for that slice with the Chart Col = [Total Count] and the Sort by = [Date] and now I have Col series Chart with aggregated data.

If I took the long road to get there please let me know.

I hope that helps someone out there.

3 Likes