cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
itchyeyeballs Established Member
Established Member

Sum earliest value based on row context

Hi all,

 

looking for some help on how to dynamically sum the earliest value for a record based on the row context. 

 

I basically want to be able to find and sum up the earliest record for each value based on the row context, the image below shows some sample data and the different outputs I'd like to achieve. The example only shows one record ID but where there are multiple I need to sum them all together for each scenario.

 

I've not had much luck searching for a solution so far so hoping you guys can help me out. I'd like to use the same DAX expression across a range of tables and visuals if possible

 

EXCEL_XWGHQIW2yL.png

1 ACCEPTED SOLUTION

Accepted Solutions
jmalone Member
Member

Re: Sum earliest value based on row context

Sorry, I missed the part about needing the single record number. I modified my original measure to add a filter condition for the record id.

 

I'm not sure why you'd want to use the SUMX in addition to the filter condition within CALCULATE. Usually, the pattern would be either:
      SUMX ( FILTER( table, conditions), expression)
        or
      CALCULATE ( expression , filter conditions)

but it's unlikely you'd need both.

 

Sum of Total  at Earliest Date =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        'Table',
        'Table'[Date]  = MIN ( 'Table'[Date] )
         && 'Table'[record_id]  = MIN ( 'Table'[record_id] )
    )
)

View solution in original post

5 REPLIES 5
jmalone Member
Member

Re: Sum earliest value based on row context

You'll want to write a measure with the following pattern. The trick is to use FILTER() to perform your sum on the records with the MIN date. The rest of the row context (i.e. RecordID, group1, group2, etc.) is dynamic based on your visual/chart.

 

Sum of Total  at Earliest Date =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        'Table',
        'Table'[Date] = MIN ( 'Table'[Date] )
    )
)
itchyeyeballs Established Member
Established Member

Re: Sum earliest value based on row context

Hi, 

 

Thank you for the response, I'm having trouble to get it to work though.

 

I need to get the minimum value for each record_id based on the row context and sum them, the example I gave only had a single record_id but the actual table has many.

 

At the moment the numbers being returned are too low.

 

Edit - I seem to be getting closer by changing to 

 

Sum of Total  at Earliest Date =
CALCULATE (
    SUMX (allselected('Table'[record_id],sum ( 'Table'[Total] )),
    FILTER (
        'Table',
        'Table'[Date] = MIN ( 'Table'[Date] )
    )
)

but my still not adding up correctly

Community Support Team
Community Support Team

Re: Sum earliest value based on row context

Hi @itchyeyeballs ,

 

Try to update your measure as below.

 

Sum of Total  at Earliest Date =
CALCULATE (
    SUMX (allselected('Table'[record_id],sum ( 'Table'[Total] )),
    FILTER (
        ALLSELECTED('Table'),
        'Table'[Date] = MIN ( 'Table'[Date] )
    )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
itchyeyeballs Established Member
Established Member

Re: Sum earliest value based on row context

Hi,

 

Still not working 😞

 

I'm not sure the formula in its current form is picking up the earliest record for each row ID, it seems to be finding the earliest record for the group.

 

Each record_id can appear across multiple groups but only its first instance in that group should be counted (hope that makes sense)

 

Edit:

I have tried switching things around to look like:

=SUMX (
    VALUES( 'Table'[record_id] ),
    CALCULATE (
        sum ( 'Table'[Total] ),
        FILTER (
            'Table',
            'Table'[Date]
                = [date_min]
        )
    )
)

date_min = calculate([min('Table'[Date]),'Table'[Total]>0)

but my totals and subtotals are way too high, I'm guessing I'm losing the row context somewhere but not sure how to fix

 

jmalone Member
Member

Re: Sum earliest value based on row context

Sorry, I missed the part about needing the single record number. I modified my original measure to add a filter condition for the record id.

 

I'm not sure why you'd want to use the SUMX in addition to the filter condition within CALCULATE. Usually, the pattern would be either:
      SUMX ( FILTER( table, conditions), expression)
        or
      CALCULATE ( expression , filter conditions)

but it's unlikely you'd need both.

 

Sum of Total  at Earliest Date =
CALCULATE (
    SUM ( 'Table'[Total] ),
    FILTER (
        'Table',
        'Table'[Date]  = MIN ( 'Table'[Date] )
         && 'Table'[record_id]  = MIN ( 'Table'[record_id] )
    )
)

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors