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.
Solved! Go to Solution.
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.
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.
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.
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.