Best approach to calculate averages over time

[Anonymous]

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

[Greg_Deckler]

Just so I am clear, with that given set of data, what are your expected results?

[Anonymous]

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.

[Anonymous]

And by the way, my table has over a million records.

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is your expected result.  Download the PBI file from here.

[Anonymous]

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.

[Ashish_Mathur]

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.

[Anonymous]

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.

[Ashish_Mathur]

You are welcome

[Anonymous]

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.

[Anonymous]

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.