Forum Discussion
Calculate Running Total Using Input from Another Calculation (Measure)
There are many solutions to calculate a running total based on dates or counts within a table, but I have not found a way to create a running total measure using input from another measure. Consider the following visualization table:
| Column A | Column B | Column C | Column D |
| Unique_ID (table data) | Medication (table data) | Percent_Contributed (calculated) | Percent_Running_Total (desired calculation: same row, column C + prior row, column D) |
| 7980 | Penicillin G | 17.77 | 17.77 |
| 723 | Amoxicillin | 5.79 | 23.56 |
| 2670 | Codeine | 4.23 | 27.79 |
| 1191 | Aspirin | 2.49 | 30.28 |
| 10831 | SMZ/TMP | 2.47 | 32.75 |
| 7052 | Morphine | 2.37 | 35.12 |
Columns A and B are text fields identifying unique items. Column C is a measure resulting from a calculated numerator and denominator [(amount for unique entry/amount for all unique entries) * 100].
I'm trying to use a measure to populate Column D in a visualization table with a running total using info from Column C, but all methods appear to rely on info in the data table(s).
5 Replies
Hi kcdistiller
Can you post sample data on oneDrive or Drop box to arrive ata solution.
Also post the formula you have used for measure ColumnC
Cheers
CheenuSing
hi kcdistiller
In terms of your data, does it have any dates associated with it?
If you have got a date, then you can easily create an interim calculated measure which will be the previous dates value.
NOTE: You will need a date table and create a relationship between the tables for this to work.
The example below is getting the values for the Previous Day.
Previous Row Data = CALCULATE ( [Actuals], PARALLELPERIOD ( 'Date'[Calendar Date], -1, DAY ), )And then your running total could be [Column D] + [Previous Row Data]
Most solutions that I've found use dates, but there are no dates associated with my data.
Hi kcdistiller
It is possible to do it without a calendar table also.
I am assuming your UniqueID ( Column A) and Medication Data ( Column B) are coming from the same table.
Try this for column D , where you need the running total of column C
Define a measure RunningTotC
RunningTotC = Calculate([measureCoumnC],
FIlter(ALL(SourceDataTable),
(SourceDataTable[ColumnA])<=MAX(SourceDataTable[CoulmnA])
))
The highlighted area above can also be changed to take a concatenation of Column A and Column B
as
( SourceDataTable[ColumnA]&"-"&SourceData[ColumnB]<=MAX(SourceDataTable[ColumnA]&"-"&SourceData[ColumnB])
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Your suggestion calculates something, but not what is expected. If there is a way to use 'EARLIER' in place of 'MAX' in your recommended code, I think it would work. However, I get an error saying that "'EARLIER' refers to a row context which dosen't exist'. This is likely due to 'Percentage' being a measure, not an actual value in a table. If I change the measure to a calculated column, I lose the ability to update the value based on slicers. Ideally, the code would probably look like
RunningTotC = CALCULATE([Percentage],Filter(ALL('My Table'),('My Table'[Percentage])>=EARLIER('My Table'[Percentage])))where 'Percentage' is the result of the measure in Column C, but 'EARLIER' does not work with measures.
