Bucketized and grouped line chart

[fedpar]

I'm stumped with how to proceed here. I have a line chart that shows the bucketized engagement time of users on our devices. However, the line chart aggregates usage per user per device, and I only want to do it per user. That's a bit vague, so below is a more detailed explanation of what I want to do.

I have a query that looks like this:Just for a quick overview of the data, people are identified by id, the device they used is identified by Device, and EngagementTime shows the time they spent using the device on a given date. I then added a new custom column to bucketize the data by engagement time, with the following code: (I'm aware I could've used a conditional column to do what's below, but the actual data has more nested conditions that didn't allow me to do that).

EngagementTimeBucket =
   if EngagementTime < 1 then
      "Less than 1 hour"
   else if EngagementTime < 4 then
      "Between 1 and 4 hours"
   else if EngagementTime < 8 then
      "Between 4 and 8 hours"
   else
      "More than 8 hours"

 I then plotted a line chart with the Date as the x-axis, and the percentage of the buckets* as the y-axis:

*to obtain the percentage, I created the following measure:

EngagementTimePercentage = 
VAR numerator = COUNTA([EngagementTimeBucket])
VAR denominator = CALCULATE(COUNTA([EngagementTimeBucket]),
	ALL([EngagementTimeBucket]))
RETURN

IF(ISBLANK(numerator), 0, numerator) / denominator

Now my problem: the chart shows the bucketized engagement time per user per device per date. However, I don't want to do it per device; I simply want to sum up the total Engagement Time per user and plot that over the date. In other words, I want to bucketize according to the Engagement Time each user spent on all devices.

I could just use Group By on my query and not include the Device column, but that would mess up other visuals in my report where I do need that column. I could also duplicate my query and Group By, but then the slicers and filters I have throughout my report wouldn't work for this new query. How can I solve this?

Thanks!

[Eric_Zhang]

---

fedpar wrote:

I could just use Group By on my query and not include the Device column, but that would mess up other visuals in my report where I do need that column. I could also duplicate my query and Group By, but then the slicers and filters I have throughout my report wouldn't work for this new query. How can I solve this?

---

@fedpar

I don't quite get the problem, but a calculated table based on that query can work with slicer and filters. The calculated table are data aggreagrated by date,user and EngagementTimeBucket, you can create EngagementTimePercentage on this table.

Table = SUMMARIZE(Table13,Table13[Date],Table13[id],Table13[EngagementTimeBucket],"aggreatedCount",COUNTROWS(Table13))

If you have any question, feel free to let me know.

[fedpar]

But...if I summarize after creating the buckets, wouldn't that lead to incorrect data? Correct me if I'm wrong, but let's say I have the following table:

And I summarize with like in your example. This should be the result:

Thus, looking at the first three rows, the buckets are now incorrectly labelled. Additionally, because I now have another table in my report, any Report level filters I apply using the original table's values will not be applied on the new table's values.

Thank you! 🙂

[Sean · Accepted solution]

@fedpar I've outlined the steps I take and formulas I use to solve this in the picture below...

And here's the result

I think this should work!

I tried doing this without a Summary table but got stuck...

Here's how far I got...

[fedpar]

That looks awesome! Guess it's not possible to do it without the additional table, then. Thank you!

[samdthompson]

assuming that the first column is user id, change the formula to:

calculate(sum([engagementime]),allexcept(table,table[userid]))/ denominator

[fedpar]

Thinking about it a bit more, I'm not sure how the proposed solution can work. Removing any column would not solve the problem, as the data would still not be grouped by the Device, and would thus perform no additional aggregation and change nothing.

Again, my guess is that the change would have to be made the moment I create the EngagementTimeBucket column, but I'm not sure how to approach this...

[fedpar]

Wouldn't I have to do something before I generate the buckets? By creating the buckets as they are now, the rows are incorrectly bucketized because they are taking the Device into account.