How to calculate the sum of one column based on the running total of another column?

[Anonymous]

Using the example below, how would I calculate the dollar amount for the first forty cases that were sold?

What would the formula be to calculate the dollar amount for a running total of the "# of Cases" column?

Example:

$ Amount   # of Cases

$100                 5

$150               20

$60                 15

$75                 10

$90                 13

$120               15

$40                   8

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You need to create an index column in query editor , then you can create the following two columns:

Running total cases = CALCULATE(SUM('Table'[# of cases]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))

Runnning total amount = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))

Use Running total cases as slicer:

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ee_C4EXmY11HgazNTBQ434wB4j4f0qWBp3kVoXpZzCq1bQ?e=6AbZm7

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[darentengmfs]

Hi @Anonymous 

You can do so by first creating a running total of # of Cases (if you have date or ID column).

Then, create a measure that sums $ amount, put that in a chart, and use the running total column as a filter, where running total <= 40.