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.
Hi. I'm relatively new to Power BI. I am working with a dataset that had resources used in an event and the duration of the event (among other variables such as who sponsored the event and the type of event). The data is set up so that each resource has its own entry. The trouble is when I try to aggregate hours, if an event has multiple resources (which most do), it will count the hours for each entry separately rather. In other words, the hours for a given event are double or triple counted.
Example: I have an event that uses 4 resources and lasts 2 hours. In my summation of hours, that event should ony count as 2 hours, but instead because there were 4 resources (each resource being used for 2 hours), the total comes out to 8.
Is there a way to restrict a sum to just 1 entry per Event ID or somehow adjust the total so that it reflects the actual number of hours? I thought maybe creating a measure that divides by the number of Event IDs would work, but it doesn't seem to.
Thanks. I appreciate your help.
Solved! Go to Solution.
Hi sfar22, if the situation is like the picture, use average function to avoid your problem.
Thanks BILASolution. It's a little bit different from what you had provided in your screenshot. If you use that as an example, I would want to find the total duration, but only counting each Event ID once. I'd like to figure out the total duration (which would be the sum of each event duration), but the problem is when I have tried to do that it totals the duration for each resource entry, which makes it seem like everything lasted much longer than it really did. I wasn't sure how to go about it though.
Using your example, what I'm trying to get the data is something like this:
Event 1 = 2 hrs
Event 2 = 3 hrs
Event 3 = 5 hrs
Total Hours = 10 hrs
Hypothetically, is there a way to take the average for each EventID and then sum them (at least that seems like that might be a work around considering each resource for each event is used the same amount of time)?
I appreciate your help!
Hi again, I hope the screenshot above helps this time
Thank you! I believe this solves the issue.
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |