Calculate Running Total Using Input from Another Calculation (Measure)

[kcdistiller]

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).

[Anonymous]

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

[kcdistiller]

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.

[GilbertQ]

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]

[kcdistiller]

Most solutions that I've found use dates, but there are no dates associated with my data.

[Anonymous]

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