The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I am very new to PBI (only been using it for a week) and I am stumped on how to approach a problem. It would seem that this would be easy.
I have a table with several columns, but need to pull out data from these three. I have to pull the average number of users for each system by each of the dates. What would be the best approach to take to do this? Should I set up a dynamic table or use a Let function or perhaps you have a better approach. What function or combination of functions should I use to total up all of the user andcreate an average per system on each of the dates.
Date | user ID | system |
1/3/2018 | 113 | 123 |
1/3/2018 | 147 | 246 |
1/3/2018 | 184 | 434 |
1/3/2018 | 754 | 123 |
1/3/2018 | 865 | 467 |
1/3/2018 | 976 | 434 |
1/9/2018 | 111 | 246 |
1/9/2018 | 357 | 434 |
1/9/2018 | 457 | 246 |
1/9/2018 | 738 | 246 |
1/9/2018 | 864 | 123 |
2/15/2018 | 111 | 246 |
2/15/2018 | 113 | 467 |
2/15/2018 | 135 | 434 |
2/15/2018 | 357 | 246 |
2/15/2018 | 357 | 434 |
2/15/2018 | 526 | 434 |
2/15/2018 | 526 | 434 |
3/1/2018 | 111 | 123 |
3/1/2018 | 111 | 246 |
3/1/2018 | 113 | 467 |
3/1/2018 | 114 | 246 |
3/1/2018 | 114 | 467 |
3/1/2018 | 147 | 434 |
3/1/2018 | 184 | 246 |
3/1/2018 | 976 | 434 |
Solved! Go to Solution.
Hi,
I believe this is your expected result. Download the PBI file from here.
Just so I am clear, with that given set of data, what are your expected results?
For each of the dates (of where there are 6 in my table) I would get and average number of users per system. So for example:
on 2/9 if there were 3 users for system 1, 8 users for system 2, 12 users for system 3, the average number of users would be =
3+8+12 = 23 divided by 3 = 7.67 on 2/9
I would need to do this for each of the 6 dates.
And by the way, my table has over a million records.
Hi,
I believe this is your expected result. Download the PBI file from here.
Ashish - QQ is Average number of users a measure, column or a new table?
I am sorry for all of the questions, but I am just so new at this.
Hi,
All i did was to create a Calendar Table by going to Modelling > New Table. I then created a relatiosnship between the Date column Data Table to the Date column of the Calendar Table. Thereafter, i wrote the measure named Average number of users.
If my offered solution does not meet your exact requirement, then please tell me the exact result you are expecting. Show a picture of the result you are expecting.
Thank you so much Ashish!! It appears to be working on the 1M records. Now I will attempt to run it against the full set of data. I really appreaciate it.
You are welcome
Ashish,
Is there any way to step through each of the processes to see what data is coming out? In other words, how can I validate my data to ensure that what I want as the end result is what I am actually getting? "Trust by verify" my results.
Ashish,
Thank you so much for taking the time to help me out on this. Seeing the calculation that you created, I am not sure that I would have gotten this far. I think you are close. I am actually trying
to calculate the average number of user by system for each date. I think this just give me the average number of users for all systems per date.
The data that I provided was a very sample set of data made up to illustrate my question. My file is very large. So I actually need to know how to duplicate your steps. I was able to follow some of what you did, but not all. I am coming to realize that PBI is a humbling tool.
I can't tell you how much I appreciate you getting me this far. I am going to try to play with the sample
set to see if I can figure it out from here.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
106 | |
105 | |
74 | |
60 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |