dynamic binning, 2 layer bin table

[cme263]

I have this working in standard excel.. but as I add some additional functionality.. bad news with MAX needing the “Ctrl+Shift+Enter” for the array. Must move to DAX and Power Pivot.

I want to be able to take a data log of timed attributes, and do 2 things. I need to bin the data by selectable time resolutions (res_bin[resol]), and chart it against the clean equivalent resolution time elements.


REALLY struggling with DAX filters…. confused by Values, SelectedAll, or CrossFilter.

I have what should be great examples online… but something is not working for me.
Let’s call this table “Data”

Region	Site	Attribute	Time Stamp	Date	Time	Hour	Min
East	Lab_3	2	10/28/2016 10:00	10/28/2016	10:00	10	0
East	Lab_3	2	10/28/2016 10:05	10/28/2016	10:05	10	5
East	Lab_3	2	10/28/2016 10:10	10/28/2016	10:10	10	10
East	Lab_3	2	10/28/2016 10:15	10/28/2016	10:15	10	15

<tbody>
</tbody>

(assorted attribute data, 24 hour a day every 5 minutes from multiple Regions and Sites)
The first 4 columns are from a imported file, and the following 4 helper columns are derived:
Date = (date(year([Time Stamp]),month([Time Stamp]),day([Time Stamp]))
Time = format(hour([Time Stamp]),"00")&":"&format(minute([Time Stamp]),"00")
Hour = hour([Time Stamp])
Min = minute([Time Stamp])

I have created a table “res_bin”. This table is my source the accumulated data binning as defined by the minutes in time, and the associated time (minutes) ranges with the hour.

index	resol	time	low	high
1	60	0	0	60
2	30	0	0	30
3	30	30	30	60
4	15	00	0	15
5	15	15	15	30
6	15	30	30	45
7	15	45	45	60

<tbody>
</tbody>

My simple totals, average, and max are fine, and when graphed at the (default) 5 minute resolution, all is well.
There are a fact a number of ‘Attibute’s, and pivot tables in motions, so slicers are in play for chart management. A Slicer for “Resolution Time” [resol] is to be the controlling element. Keep getting an error… help.

res_bin_SUM:=if(
ISFILTERED(res_bin[resol]),
If(
HASONEVALUE(res_bin[resol]),
CALCULATE(SUM([ATTRIBUTE]),
FILTER(res_bin, value([min])>=values([low]) && value([min])<values([high]))),
BLANK()
),
SUM([ATTRIBUTE])
)

{in the above, ‘SUM’ could be replaced with MAX, AVERAGE, MIN or DIV(attribute1/attribute2) for a weighted average, etc.
The charted pivot tables would have the legend (series) of Region, Site, with the axis being resol, date, and time }


Chris

 

[Matt Allington]

I am confident that the simple binning can be done...

Binning is simple - you just need a row per item with some "value" to be binned. I don't see this in your data.

 

[cme263]

Binning is simple - you just need a row per item with some "value" to be binned. I don't see this in your data.

Hey Matt, I want to bin any attribute data (People Count as an example) by the date, hour, and time to the minutes within the hour [min] bin (as defined by the res_bin table) for the selectable time resolutions.

 

[Matt Allington]

This is very hard to communicate on this topic over a forum, but I am trying . You need numeric data if you want to create bins - 1 data point per record. If I had something like this.

cust #    WaitTime (mins)
1              4
2              7
3              3
4              4
3              8
5              3
6              4

The above data can be turned in to "wait time bins" as follows

cust #    WaitTime (mins)   BIN
1              4                     0 - 5
2              7                     6 - 10
3              3                     0 - 5
4              4                     0 - 5
3              8                     6 - 10
5              3                     0 - 5
6              4                     0 - 5

Once the bins are created (they are static), then you can create a histogram with the following data

BIN    Count of Cust
0 - 5       5
6 - 10     2

I don't see any data in your tables that is equivalent to "WaitTime" in my example. I see a time stamp, but that adds no value for this bin exercise.

 

[cme263]

Sigh! Please go back to the first post... I have seen many variation posted online, but they are always binning the column being calculated... and that is NOT what I want the bin filtering/binning. I want to have a dynamic / selectable binning option on the time (within the hour) resolution, where he various column data / attributes are summed up per the grouping of the defined time bins, i.e. a 5 minute bin, would just be all my reported data, while a 30 minute resolution time, would sum the 6 data points 00 to <:30 reported at :00 while the :30 would report the sum of the data for :30 to <:60. The disconnect bin table has nothing to do the the values of the column being summed, it for the related time.
The SUMIF in excel, will sum the array column, while any other columns are conditioned/tested and set row by row.

 

[Matt Allington]

So if I now understand correctly, you don't want to bin the data, but you want to view the data in selectable time windows. So why not just add a time slicer to your report and select the period you want to look at? This is standard Excel.

 

[cme263]

I have a time slicer in play, it works great to manage what is displayed. But that does not do the resolution time binning(summing) function I need to see for the resulting pivot table. For a 5 minute resolution, I would chart 12 data points per hour, with a 15 minute resolution I expect to see just 4 data points (at 15 minute intervals) where the totals within the hour still match of course. 30 and 60 minute resolution too!

 

[Matt Allington]

I completely fail to understand what you are trying to do - sorry

 

[Matt Allington]

As i have tried to explain, banding needs a column of values (e.g. Sales) with one row per transaction. I don't see this column in your data hence I see no way to create a band. If you had a table with 1 row per customer, the time they arrived and the time they left, and total wait time, then you can do it. But that doesn't seem to be what you have. There is no point following a pattern you find if your data is not of the same format/shape.



From what I recall, you have snapshots of the number of people in a line by time stamps. I don't know how you can tell if a line of 5 people on the hour is the same 5 people at 5 past the hour or even an hour later. If you want to measure average line length, then that is a different thing altogether and I see no reason why that wouldn't work, but I understood you were trying to determine average wait time (maybe I'm wrong there).

 

[cme263]

As i have tried to explain, banding needs a column of values (e.g. Sales) with one row per transaction. I don't see this column in your data hence I see no way to create a band. If you had a table with 1 row per customer, the time they arrived and the time they left, and total wait time, then you can do it. But that doesn't seem to be what you have. There is no point following a pattern you find if your data is not of the same format/shape.

From what I recall, you have snapshots of the number of people in a line by time stamps. I don't know how you can tell if a line of 5 people on the hour is the same 5 people at 5 past the hour or even an hour later. If you want to measure average line length, then that is a different thing altogether and I see no reason why that wouldn't work, but I understood you were trying to determine average wait time (maybe I'm wrong there).

Chris adds... I do not want to band the values that will be calculated, I want the banding to be applied to time, specifically the minute that the data was reported. Please don't stress about the data meaning or integrity. The data log has many data sources (defined by the first 3 columns), followed by some columns of data, ending with a column for the time stamp (year,month,day, hour,min, sec). All time stamps are every 5 minutes.
I have created an unconnected table, where the time resolution bands are defined. 5, 15, 30, and 60 minutes bands as an example.
In excel, this would be a =sumif('datacolumn', A2=columnA, B2=columnB, C2=columnC, date=datecolumn, hour=hourcolumn, min=<the minute banding as defined by the resolution>). The problem I cannot figure out, is all DAX examples have the banding of the datacolumn... and I don't. I want the sum of datacolumn, by the date to the minute defined by the date banding - which is to be selectable. The calculate(sum.. (or sumx) will automatically take care slicing of the legend for columnA, columnB, and columnC; and the date and time once the time is set to the selectable time resolution bands on the horizontal axes.
In my power pivot, I can build helper columns, were I hard code the time resolution, and start to get a pattern I want, but the call to the unrelated table generates an error.

 

[yaswanth0291]

But without Dax can we do with grouping? Is it possible? Can anyone explain me