Frequency distribution in PowerPivot

bertusavius

Board Regular
Joined
Feb 28, 2008
Messages
82
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)?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top