Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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] ) ) )
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] ) ) )
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
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] ) ) )
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] ) ) )
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |