Frequency distribution in PowerPivot

[bertusavius]

I have some experience in creating frequency distributions in Excel using SUMPRODUCT, VLOOKUPS or Array formulas.

But at the moment I am trying to use Power Pivot for various reasons (amongst other things the size of the dataset)

What would be the best approach to creating a frequncy distribution in the following situation:

---

I have a table in which every row contains a time value (hh:mm:ss)

I have another table in wich the distribution is defined like this:
start ----| stop ----| code
06:00:00 | 06:59:00 | 6-7
07:00:00 | 07:59:00 | 7-8
etc (open for changes)

---

Can I create a calculated column in which the code is assigned to each row, or is there a better way (perhaps within the pivot table)?

 

[powerpivotpro]

Just to make sure I understand: the table that contains time values is relatively large, and is your actual data, correct?

And the other table defines the "buckets," and you want to assign values from the data table to those buckets?

 

[bertusavius]

The actual data is correct. The only thing that lacks is the relation between The (correct) times and the 'bucket'

The other table does indeed represent the buckets.

 

[powerpivotpro]

A more complex version of your problem is covered in the second half of this post:

http://www.powerpivotpro.com/2012/0...e-with-precise-measurements-in-another-table/

The crux though is that a calculated column in your data table using CALCULATE with one or more FILTER's is going to be the basis of the solution.

You will also need to create integer versions of the start/stop times in your buckets table. (Convert them to pure seconds - hours * 3600 + mins * 60 + seconds). That way you can use > and similar operators in your FILTERs.

 

[bertusavius]

This looks very promising.

I will update this thread asap with the result.

Thank you very much.

 

[bertusavius]

Just back to inform you that your solution did the trick perfectly.

I also found that it wasn't neccecary to convert the time values into integers; the results were the same as far as I could tell.


Really appreciate your help in the matter.

 

[powerpivotpro]

Excellent! Very glad that worked for you.

 

[ruve1k]

bertusavius,
If your buckets are consistently sized you may be able to use a rounding technique on your actual data and then utilize a native relationship with your buckets table.