cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mlrossi Regular Visitor
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. 

 

Dateuser IDsystem
1/3/2018113123
1/3/2018147246
1/3/2018184434
1/3/2018754123
1/3/2018865467
1/3/2018976434
1/9/2018111246
1/9/2018357434
1/9/2018457246
1/9/2018738246
1/9/2018864123
2/15/2018111246
2/15/2018113467
2/15/2018135434
2/15/2018357246
2/15/2018357434
2/15/2018526434
2/15/2018526434
3/1/2018111123
3/1/2018111246
3/1/2018113467
3/1/2018114246
3/1/2018114467
3/1/2018147434
3/1/2018184246
3/1/2018976434
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Best approach to calculate averages over time

Hi,

 

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

 

Untitled.png

10 REPLIES 10
Super User
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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


mlrossi Regular Visitor
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.

mlrossi Regular Visitor
Regular Visitor

Re: Best approach to calculate averages over time

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

 

Highlighted
Super User
Super User

Re: Best approach to calculate averages over time

Hi,

 

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

 

Untitled.png

mlrossi Regular Visitor
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.

mlrossi Regular Visitor
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.

mlrossi Regular Visitor
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
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.

mlrossi Regular Visitor
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.