Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fedpar
Employee
Employee

Bucketized and grouped line chart

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:Capture.PNGJust 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:

Capture.PNG*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!

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

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

 

Bucketized and Grouped Line Chart 1.png

 

And here's the result

 

Bucketized and Grouped Line Chart 2.png

I think this should work!

 

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

Here's how far I got...

 

Bucketized and Grouped Line Chart 3.png

View solution in original post

7 REPLIES 7
Eric_Zhang
Employee
Employee


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


Capture.PNG

 

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

 

 

 

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:

Capture0.PNG

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

Capture.PNG

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
Community Champion
Community Champion

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

 

Bucketized and Grouped Line Chart 1.png

 

And here's the result

 

Bucketized and Grouped Line Chart 2.png

I think this should work!

 

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

Here's how far I got...

 

Bucketized and Grouped Line Chart 3.png

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

samdthompson
Memorable Member
Memorable Member

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

 

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

// if this is a solution please mark as such. Kudos always appreciated.

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

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.