cancel
Showing results for
Did you mean:
Frequent Visitor

## Need help on table or formula

Hello,

I'm having issue regarding formula, I'm new to this languange and currently studying it.

here's what I got stucked.

So I have this Table

 Item# Time Stamp Hour Quantity A 1:43:01 PM 13 1 B 1:48:01 PM 13 1 C 1:47:01 PM 13 1 A 2:39:03 PM 14 1 B 2:43:12 PM 14 1 C 3:59:13 PM 15 1 C 4:03:53 PM 16 1 C 4:12:29 PM 16 1 C 4:12:29 PM 16 1

But I need to summarize the report by hourly to show on the graph.

The challenging part for me is I need to rank by hour the item # thats the earliest (or lowest timestamp), and sum the total results under all hours. If it doesnt make sense. basically these is what im looking for.

Rank lowest time stamp - shows the item# with the lowest time stamp for that hour.

 Hour Rank Lowest Time Stamp Results 13 A 3 14 A 2 15 C 1 16 C 3

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Need help on table or formula

@pamboys09  Please try this as a "New Table"

```Test11Out =
VAR _Time = ADDCOLUMNS(SUMMARIZE(Test11,Test11[Hour],"MinTime",MIN(Test11[Time Stamp])),"Item",LOOKUPVALUE(Test11[Item#],Test11[Hour],[Hour],Test11[Time Stamp],[MinTime]))
VAR _Totals = SUMMARIZE(Test11,Test11[Hour],"Results",SUM(Test11[Quantity]))
RETURN NATURALINNERJOIN(_Time,_Totals)```

Added the time as well, just for reference.

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

Proud to be a Datanaut !

2 REPLIES 2
New Contributor

## Re: Need help on table or formula

Maybe look to add an index/ranking column within each hour in your dataset and use the lowest value of said index as a filter? Ranking within a group's a fairly common problem so you should be able to search and find something that works within your setup?

Super User

## Re: Need help on table or formula

@pamboys09  Please try this as a "New Table"

```Test11Out =
VAR _Time = ADDCOLUMNS(SUMMARIZE(Test11,Test11[Hour],"MinTime",MIN(Test11[Time Stamp])),"Item",LOOKUPVALUE(Test11[Item#],Test11[Hour],[Hour],Test11[Time Stamp],[MinTime]))
VAR _Totals = SUMMARIZE(Test11,Test11[Hour],"Results",SUM(Test11[Quantity]))
RETURN NATURALINNERJOIN(_Time,_Totals)```

Added the time as well, just for reference.

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

Proud to be a Datanaut !

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)