Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Cumulative Total Per Hour & ID

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 

UniqueIDdatehourID2units sold
10012024-03-12 13Bob5
10022024-03-12 13Bob3
10032024-03-12 13Bob2
10042024-03-12 13Lisa4
10052024-03-12 14Bob3
10062024-03-12 14Lisa4
10072024-03-12 14Adam6
10082024-03-12 15Bob2
10092024-03-12 15Bob3

 

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. 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    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.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Anonymous Maybe:

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    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.