cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
1.PNG

 

Table visual
2.PNG

 

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)

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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