Count as 1 when it happens in the same minute

[rpinxt]

Sorry for the really abstract title but this is a bit of a strange one.

We have this :

For the first 2 lines you see there is 12 seconds between them.

I made Bins to group everything in the same minute because I want the EA/Carton field to show 1.

So if it was confirmed in the same minute it is only 1 occurence.

When I take out confirmation time everything looks ok :

2:27 showing only 1 EA/Carton. But the field is not summed. And of course this is only 1 line.

In the end I want to sum the occurences and make sure that everything in the same minute is counted as 1.

But when I put sum back on on the field EA/Carton :

Then it still counts as 2.

Would there be a good way to count everything that happens in the same minute as 1 occurence?

Here a snippet test data to play around with :

Confirmation time	Confirmation time 1 Min Bin	Source Storage Bin	EA/Carton	Sum of Act.qty (dest)
02:27:18	02:27:00	CQ0505A	1	1
02:27:30	02:27:00	CQ0505A	1	1
05:32:55	05:32:00	CQ0505A	1	1
10:36:36	10:36:00	CQ0505A	1	1
10:36:51	10:36:00	CQ0505A	1	1
12:00:48	12:00:00	CQ0505A	1	1
12:23:14	12:23:00	CQ0505A	1	2
12:23:32	12:23:00	CQ0505A	1	1
12:40:47	12:40:00	CQ0505A	1	1
12:47:08	12:47:00	CQ0505A	1	1
14:03:06	14:03:00	CQ0505A	1	1
14:37:35	14:37:00	CQ0505A	1	1
14:39:01	14:39:00	CQ0505A	1	1
14:53:28	14:53:00	CQ0505A	1	1
14:56:32	14:56:00	CQ0505A	1	2
15:05:23	15:05:00	CQ0505A	1	1
15:35:18	15:35:00	CQ0505A	1	1
15:35:28	15:35:00	CQ0505A	1	1
15:35:41	15:35:00	CQ0505A	1	1
15:35:52	15:35:00	CQ0505A	1	1
15:36:06	15:36:00	CQ0505A	1	1

[rpinxt]

Thanks but that is not an option. Data is coming from our system and date/time comes in that format.

Also this is only small part of the views. In other views we would like precise timing.

[Wilson_ · Accepted solution]

rpinxt,

Got it. One solution you can try is:

Sum of EA/Carton =
VAR Summarized =
SUMMARIZE (
    Table1,
    Table1[Confirmation time 1 Min Bin], Table1[Source Storage Bin], Table1[EA/Carton]
)
VAR Result =
SUMX (
    Summarized,
    [EA/Carton]
)

RETURN Result

What this does is do the aggregation first before summing the EA/Carton column. I wasn't fully clear on what you were looking for as an end result so let me know if I'm a little off the mark. The overall concept is probably correct though.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[rpinxt]

Thanks @Wilson_ think we can work with this.

[Wilson_]

Hi rpinxt,

Do you ever care about the seconds in the confirmation time? Will you ever need to analyze data at the second level of granularity? If not, the easiest solution is to fix the grain of your fact table in the data source so you don't have two different lines for 02:27 in your data model in the first place

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.