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 attempting to show the cost total per year and the cumulative (running total) kW with each successive year. However the running total quick measure is not working correctly.
Instead of showing a cumulative total which grows with each year, it is showing the kW total for each year multiplied by the position order of each year sequentially. So the first year with a kW value in the table appears correctly but the second year shows the total for that year (ignoring the first year) multiplied by 2, the third year shows the third year total multiplied by 3, etc. Years with no projects are skipped over. What am I missing here?
Below is the DAX input and the data I am getting back with the sum of kWs also shown (correctly) for each year.
The DAX input:
I used the same DAX input with a simple set of data that I randomly created and I get the correctly calculated running total so I know the measure is written approriately.
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])))
4 Replies
Hi Anonymous ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
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
- 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])))