cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Subtotal column based on time and text column

I have a data that looks like this

Date | Time | Text | Sum
1-1-2001 | 12:23:34 | a| 2
1-1-2001 | 12:23:34 | b| 3
1-1-2001 | 12:25:34 | a| 2

1-1-2001 | 12:26:34 | b| 2
1-1-2001 | 13:23:34 | a| 1
1-1-2001 | 13:24:34 | a| 2

I need a result with something like this
Text | Sum
a | 4
b | 5
a | 3

or even just the sum column.

The first and third row are grouped because they are within a 5 minute range. Similarly with 2, 4  and 5,6. My end goal is to determine the frequency of each sum value in the data. It is a large dataset with multiple text values and date values.

3 REPLIES 3
Highlighted
Microsoft

## Re: Subtotal column based on time and text column

Hi @prakhar,

Based on my understanding, you need to sum the column [Sum] for each [Text] type every 5 minutes, right?

In this scenario, please create some calculated columns, referring to below DAX formulas:

```Min Time for each text =
CALCULATE (
MIN ( 'SubTotal Column'[Time] ),
ALLEXCEPT (
'SubTotal Column',
'SubTotal Column'[Date],
'SubTotal Column'[Text]
)
)

Difference Minute =
HOUR ( 'SubTotal Column'[Min Time for each text] - 'SubTotal Column'[Time] )
* 60
+ MINUTE ( 'SubTotal Column'[Time] - 'SubTotal Column'[Min Time for each text] )
+ SECOND ( 'SubTotal Column'[Time] - 'SubTotal Column'[Min Time for each text] )
/ 60

Interval(every 5 minute) =
INT ( 'SubTotal Column'[Difference Minute] / 5 )

Total Sum =
CALCULATE (
SUM ( 'SubTotal Column'[Sum] ),
ALLEXCEPT (
'SubTotal Column',
'SubTotal Column'[Date],
'SubTotal Column'[Text],
'SubTotal Column'[Interval(every 5 minute)]
)
)```

Data view

Table visual

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Subtotal column based on time and text column

Hi @v-yulgu-msft,

Thanks for the reply. That is not exactly what I need.

I was also thinking of a similar solution. But with this solution, the time is divided into fixed 5 minute intervals. I don't want to divide the time. I want to put in an if condition that is based on whether the rows that need to be grouped are within 5 minutes of each other or not. eg. by dividing into fixed intervals, 12:20 and 12:24 text are grouped and  12:29 and 12:31 won't be grouped. I need both the groupings to occur

What I think would be better is to compare rows and if two rows with same Text are within 5 minutes of each other, then add their sum values. I am not sure how this could be implemented in Power BI. I saw EARLIER function used for such cases but haven't been able to write query using them or even understand them properly.

Regards
Prakhar

Highlighted
Frequent Visitor

## Re: Subtotal column based on time and text column

I was able to create what I needed using your idea of adjusted time. I did this:

`Adjusted Time = CALCULATE(                MIN('items'[Time]),                ALL('items'[Date])                   ALL('items'),                HOUR('items'[Time]-EARLIER('items'[Time]))*60                +MINUTE('items'[Time]-EARLIER('items'[Time]))<=5)`

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors