cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imran_uridetech
New Member

Summarize

TempTable.jpgResult.jpg

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.

 

1 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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
az38
Super User II
Super User II

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 )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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
Visual1.jpg

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors