Summarize

[imran_uridetech]

I am trying to Summarize a Table and calculate running total in a new Table
As you can see in the attached pictures, the TempTable (1st Picture) with its DAX codes
and when I try to SUMX the Column "Summary" from TempTabel to my result table its not workig at row level, I want to add up only if the values in [Order] is less than or equal to the row value in my result table [PnL_Order], somthing similar to a running total.

Any help is appreciated.

[az38]

hi @imran_uridetech 

not the best but maybe quickest solution

var result =
summarize(pnl, [Pnl_Order], "Amount1", 
var _curOrder = MAX ([Pnl_Order]) 
RETURN
CALCULATE(SUMX(temptable, [Summary]), temptable[Order] <= _curOrder )

[imran_uridetech]

I cannot use "temptable[Order]" its coming from VAR above, so instead I tried to use it inside a FILTER and the result is same:

VAR Result1=SUMMARIZE(PnL,[PnL_Order],"Amount1",

VAR _curOrder=MAX(PnL[PnL_Order])

Return CALCULATE(SUMX(TempTable,[Summary]),FILTER(TempTable,[Order]<=_curOrder)))

[mahoneypat · Accepted solution]

Please try it with this change

VAR Result1=SUMMARIZE(PnL,[PnL_Order],"Amount1",

VAR _curOrder=MAX(PnL[PnL_Order])

Return SUMX(FILTER(TempTable, [Order] <=_curOrder), [Summary])

Pat

[imran_uridetech]

Do you have any thoughts about why the same concept would not give me a result in all rows, when I use it in a Measure instead of a Table

ReportAmount2 =

VAR VAR_Table=SUMMARIZE(fData_2021B,[Order],"Amount1",SUMX(fData_2021B,[PnL_SAR]))

VAR ResultTable=SUMMARIZE(PnL,[PnL_Order],[ORM_FS_1],[Display],[Factor],"RunningAmount",SUMX(VAR_Table,[Amount1]))

VAR RunningAmount=

VAR VAR_CurOrder=MAX([PnL_Order])

RETURN SUMX(FILTER(ResultTable,[PnL_Order]<=VAR_CurOrder),[RunningAmount])

Return RunningAmount