cancel
Showing results for
Did you mean:
Regular Visitor

## Best approach to calculate averages over time

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Hi,

10 REPLIES 10
Super User

## Re: Best approach to calculate averages over time

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

Proud to be a Datanaut!

Regular Visitor

## Re: Best approach to calculate averages over time

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.

Regular Visitor

## Re: Best approach to calculate averages over time

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

Highlighted
Super User

Hi,

Regular Visitor

## Re: Best approach to calculate averages over time

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.

Regular Visitor

## Re: Best approach to calculate averages over time

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.

Regular Visitor

## Re: Best approach to calculate averages over time

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.

Super User

## Re: Best approach to calculate averages over time

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.

Regular Visitor

## Re: Best approach to calculate averages over time

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.