topic Re: DAX Fix from groupby to a SUM in DAX Commands and Tips

DAX Fix from groupby to a SUM

bstark1287 — Mon, 12 Dec 2022 21:03:35 GMT

I have the below DAX which returns the count of [Order/line] if the order is open during a time period. (shows total open orders for each month in history based on [CREATE_DATE] and [Order Closed Date1]). I need to adjust the DAX to SUM the calculated column [Order_Qty_adj]. I was hoping I could just adjust [ID] to [Order_Qty_adj] and tmpTable1 to SUMX on [ID].

Open Tickets =

VAR tmpTickets = ADDCOLUMNS('OB Raw Order Data1',"Effective Date",IF(ISBLANK([Order Closed Date1]),TODAY(),[Order Closed Date1]))

VAR tmpTable =

SELECTCOLUMNS(

FILTER(

GENERATE(

tmpTickets,

'tbl_Calendar'

AND(

([Date] >= [CREATE_DATE]) && ([Date] <= [Effective Date]),

OR(([Order Closed Date1] > [End Of Month]), ISBLANK([Order Closed Date1]))

)

"ID",[Order/Line],

"Date",[Date]

)

VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

Re: DAX Fix from groupby to a SUM

Anonymous — Tue, 13 Dec 2022 02:03:34 GMT

Hi bstark1287 ,

Maybe you can try the following code:

VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",SUMX(CURRENTGROUP(),SUM([Order_Qty_adj])))

You can refer to the following link:

Power BI Group By | How to Use GROUPBY DAX Function in Power BI? (wallstreetmojo.com)

If the information above cannot meet your requirement, can you offer some data example or picture?

Best Regards!

Yolo Zhu

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: DAX Fix from groupby to a SUM

bstark1287 — Tue, 20 Dec 2022 16:14:39 GMT

I ended up deleting the measure entirely and starting from scratch. Because the Order_Qty_adj is a calculated column this was the route I believe best suited.

OB QTY =

VAR _Date = MIN ( 'tbl_Calendar'[End Of Month] )

VAR _EoM = MIN ( 'tbl_Calendar'[End Of Month] )

RETURN

CALCULATE (

SUM ( 'OB Raw Order Data1'[Order_Qty_adj]),

'OB Raw Order Data1'[CREATE_DATE] <= _Date,

( 'OB Raw Order Data1'[Order Closed Date1] > _EoM || ISBLANK ( 'OB Raw Order Data1'[Order Closed Date1] ) )

)