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 ,
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