Forum Discussion
Solved
Accumulative values from a count
Hi All
I am struggling to plot data that accumulates the values from a count.
To illustrate what I am trying to do I have created the table in Excel
| Item Name | Cost Per Item x Number Sold | Timestamp | Total |
| Box | 6 | 01/02/2017 | 6 |
| Box | 56 | 02/02/2017 | 62 |
| Box | 8 | 07/05/2017 | 70 |
| Letter | 12 | 06/05/2017 | 12 |
| Letter | 577 | 04/08/2017 | 589 |
| Letter | 78 | 17/01/2017 | 667 |
The total column is the column that I am unable to create in PowerBI. Does anyone know how to do this in a DAX expression?
Thanks, Ashley
You may also refer to the following DAX that creates a measure.
Measure = VAR n = MAX ( Table1[Item Name] ) VAR t = MAX ( Table1[Timestamp] ) RETURN CALCULATE ( SUM ( Table1[Cost Per Item x Number Sold] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Item Name] = n && Table1[Timestamp] <= t ) )
4 Replies
Hi Ashley ahorne27
Add this Calculated Column to get Cumulative Values or Running Totals :smileywink:
RunningTotal = SUMX ( FILTER ( Table1, Table1[Item Name] = EARLIER ( Table1[Item Name] ) && Table1[Timestamp] <= EARLIER ( Table1[Timestamp] ) ), Table1[Cost Per Item x Number Sold] )You may also refer to the following DAX that creates a measure.
Measure = VAR n = MAX ( Table1[Item Name] ) VAR t = MAX ( Table1[Timestamp] ) RETURN CALCULATE ( SUM ( Table1[Cost Per Item x Number Sold] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Item Name] = n && Table1[Timestamp] <= t ) )Hi ahorne27,
You may also try this calculated column formula
=CALCULATE(SUM(Table1[Cost Per Item x Number Sold]),FILTER(Table1,Table1[Item Name] = EARLIER(Table1[Item Name])&&Table1[Timestamp]<=EARLIER(Table1[Timestamp])))
Hope this helps.
Thanks everyone for such quick replies! In the end the measure worked best with my content.
