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