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