Cumulative Total Per Hour & ID

[thne123]

I had a similar issue to this that I was able to solved by using the group by function in transform data. But due to table structure I don't see how this will be possible now, so I'm checking here for assistance.

I'm trying to calculate a how often a specific condition appears in a table. But I'm not getting it to work as intended due to how the data appears in the table, at least for the method I'm trying.

This is the base measure that it all depends on. This one works as intended:

Sold Units = 
CALCULATE(
    SUM('Table1'[Unit]),
        table2[A] = "filter1",
        table3[B] = "filter2"
    )

Now I want to calculate in Table1 how many hours have a "Sold units" above 500

My issue is that I'm not sure how to make sure it sums per hour, instead of the total

A simple calculation like this doesn't seem to work.

 COUNT(Table1[Hour]), 
    FILTER(Table1, _x>=500))

My initial thought was to use the group by function and transform the data. But since the columns I filter by are from other tables and the connection to the tables are by a unique ID this won't help.

Not sure how to proceed here

Each row has unique ID that is used to filter out the correct things that we want to sum.

One hour can have multiple rows

ID date/hour ID2 units sold 

UniqueID	date	hour	ID2	units sold
1001	2024-03-12	13	Bob	5
1002	2024-03-12	13	Bob	3
1003	2024-03-12	13	Bob	2
1004	2024-03-12	13	Lisa	4
1005	2024-03-12	14	Bob	3
1006	2024-03-12	14	Lisa	4
1007	2024-03-12	14	Adam	6
1008	2024-03-12	15	Bob	2
1009	2024-03-12	15	Bob	3

Based on this I want to calculate how many hours each ID2 has sold more or equal to 5. (>=5) So per example above 

Hour 13 Bob sold 5+3+2, which is >=5, so that should be 1 hour counted. 

Hour 14 Bob sold 3, should not be counted

Hour 15 Bob Sold 2+3 = 5, should be counted. 

This means that I want my measure to return 2 for Bob (when ID2 listed in a table etc)

I need the Unique ID to be able to filter out specific products. 

[v-jianpeng-msft · Accepted solution]

Hi, @thne123 

Based on your description, I use the following sample data:

I created an index column as shown in the image below:

I use the following DAX to add a tag column, the main purpose is to determine whether the number of sales of the same ID2 is greater than or equal to 5 at the same time:

Marker =
IF (
    CALCULATE (
        SUM ( 'Table'[units sold] ),
        FILTER (
            'Table',
            'Table'[hour] = EARLIER ( 'Table'[hour] )
                && 'Table'[ID2] = EARLIER ( 'Table'[ID2] )
        )
    ) >= 5,
    "Yes",
    "No"
)

Here are the results:

I use the following DAX to return a count of 5 or greater for each ID2 sale:

ISabove5 =
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[ID2],
        "count",
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        SUMMARIZE ( 'Table', 'Table'[hour], 'Table'[ID2], 'Table'[Marker] ),
                        'Table'[Marker] = "Yes"
                    )
                )
            )
    )
RETURN
    IF ( SUMX ( _table, [count] ) = 0, "", SUMX ( _table, [count] ) )

Here are the results:

I've provided the PBIX file used this time below. It would be great if it could help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@thne123 Maybe:

Measure =
  VAR __Table = SUMMARIZE( 'Table', [hour], "__Sold", SUM( [units sold] ) )
  VAR __Result = COUNTROWS( FILTER( __Table, [__Sold] >= 5 ) )
RETURN
  __Result

[thne123]

@Greg_Deckler 

Thanks!

Still not really getting the expected outcome sadly, but it's almost there. 

It now counts the correct number of rows, BUT, I still need a way to get the filters that I mentioned in my "Sold Units" measure earlier. 

I tried adding a variable that is the measure and use that instead of 

"__Sold", SUM( [units sold] ) )

So that it instead was 

"__Sold", _unitssold)

This just gave me a count of all the hours in that day instead. 

Same if I tried adding filtes within a Calculate in the result