Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Morning,
I need to create a table visulation that displays the customer, support hours used by month (within the current calendar year), total support hours used (within the current calendar year), and the remaining support hours available.
Each customer is allowed 50 support hours during a calendar year. The support hours available is 50 - total support hours used (within the current calendar year). Each customer may have multiple tickets and the support hours used within each ticket can increase or decrease throughout the month, therefore I need the most recent values each month.
If the customer doesn't have any hours used, I'd rather them not be listed in the table.
I've tried using several measures, but I just can't seem to get it worked out.
Thank you
Solved! Go to Solution.
Ahh, I see now. Ok, I think I've got it. It's more complicated than I thought. I might have over-complicated as well; there might be potential to simply the solution and combine some of these fields together. In addition to the "Hours Used Flag" I created earlier, I created four more columns in the data set:
Hi,
I set up a basic little dataset that looks like this:
I also created a calculated column for "Year" once I got to PowerBI:
Basic aggregation will sum the "Hours Used" column into our "Hours Used per Month" column. I had to create the other two metrics we're looking for as calculated measures:
@Anonymous - Thank you for helping me! The issue I'm having a hard time resolving is getting the latest value for each ticket within each month. Referencing the example you provided, the last value entered for Joe is Jan. is 1 thus, the value for Jan. should be 1. I apologize for not explaining this very well.
I also realize that I forgot to mention that each ticket's hours must be subtracted from the ticket's hours the previous month. I included some data as an example...hopefully, it helps some.
Thank you!
In your example, shouldn't Joe's value for Feb be 2 (ticket A1 on 2/10) and Susan's be 5 (ticket A2 on 2/5)? You have them in your chart at the bottom as 1 and 3. Or am I missing something still?
@Anonymous, Hi, the value for Feb needs to only show the hours used in Feb. - so I'm not double counting hours. The overall total hours used will be the sum of hours used each month. Hope that makes sensebut please let me know if I need to clarify more.
Thank you
Ahh, I see now. Ok, I think I've got it. It's more complicated than I thought. I might have over-complicated as well; there might be potential to simply the solution and combine some of these fields together. In addition to the "Hours Used Flag" I created earlier, I created four more columns in the data set:
Wow - this is perfect. Thank you so much!
If that's the case, I think I have a solution. I created a calculated column that flags the records to pull for each ticket/customer/month:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |