Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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
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.
User | Count |
---|---|
127 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |