Forum Discussion
Running Total Quick Measure not calculating correctly
I have a table of projects listed with kW values and a start year as well as other characteristics. There can be multiple projects per year (or none) depending on what is selected on the slicer. I am...
Hi Anonymous,
You can try to use the following measure formula if it suitable for your requirement:
formula = VAR _start = MINX ( ALLSELECTED ( 'Data Table' ), [Start Year] ) VAR currYear = MAX ( 'Data Table'[Start Year] ) VAR summary = SUMMARIZE ( FILTER ( ALLSELECTED ( 'Data Table' ), [Building] IN VALUES ( 'Data Table'[Building] ) ), [Start Year], [Building], "Total", VAR currValue = SUM ( 'Data Table'[Electrification kW] ) RETURN IF ( currValue <> 0, DIVIDE ( currValue, [Start Year] - _start + 1 ) ) ) RETURN SUMX ( FILTER ( summary, [Start Year] <= currYear ), [Total] )Regards,
Xiaoxin Sheng- I'm not sure why but removing "[Start Year]" from the ALLSELECTED prompt resolved my issue. This also works without using ISONORAFTER as shown in the second option below. Anonymous 's solution may work as well but I haven't given it a shot yet.Working Input #1:Electrification kW running total in Start Year =CALCULATE(SUM('Data Table'[Electrification kW]),FILTER(ALLSELECTED('Data Table'),ISONORAFTER('Data Table'[Start Year], MAX('Data Table'[Start Year]), DESC)))Working Input #2:Electrification kW running total in Start Year =CALCULATE(SUM('Data Table'[Electrification kW]),FILTER(ALLSELECTED('Data Table'),'Data Table'[Start Year] <= MAX('Data Table'[Start Year])))
Hi Anonymous, thanks for the suggestion! I can't share the full pbix but below is a table of the input data that is fed into the chart.
| Building | Electrification kW Add | Capital Cost | Start Year |
| A | 0 | $1,902,734 | 2026 |
| B | 0 | $1,735,180 | 2025 |
| C | 142 | $249,500 | 2025 |
| D | 482 | $756,000 | 2027 |
| D | 120 | $22,000 | 2027 |
| E | 174 | $1 | 2029 |
| E | 261 | $488,350 | 2029 |
| F | 984 | $1 | 2032 |
| F | 380 | $834,150 | 2032 |
| G | 376 | $1 | 2031 |
| H | 0 | $1,806,621 | 2034 |
| J | 336 | $631,250 | 2026 |
| J | 617 | $19,295,000 | 2026 |
| K | 0 | $1,267,386 | 2026 |
Hi Anonymous,
You can try to use the following measure formula if it suitable for your requirement:
formula = VAR _start = MINX ( ALLSELECTED ( 'Data Table' ), [Start Year] ) VAR currYear = MAX ( 'Data Table'[Start Year] ) VAR summary = SUMMARIZE ( FILTER ( ALLSELECTED ( 'Data Table' ), [Building] IN VALUES ( 'Data Table'[Building] ) ), [Start Year], [Building], "Total", VAR currValue = SUM ( 'Data Table'[Electrification kW] ) RETURN IF ( currValue <> 0, DIVIDE ( currValue, [Start Year] - _start + 1 ) ) ) RETURN SUMX ( FILTER ( summary, [Start Year] <= currYear ), [Total] )Regards,
Xiaoxin Sheng