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
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
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
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.