Forum Discussion

imran_uridetech's avatar
imran_uridetech
Frequent Visitor
5 years ago
Solved

Summarize

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 ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
imran_uridetech
Frequent Visitor
5 years ago

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's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
    imran_uridetech
    Frequent Visitor
    5 years ago

    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