cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Community Support

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

2 REPLIES 2
Community Support

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

Post Prodigy

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.