DAX Fix from groupby to a SUM

[bstark1287]

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)

[bstark1287 · Accepted solution]

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

    )

[v-xinruzhu-msft]

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.}