GROUPBY, COUNTX and SUMX for several columns

[Yaro]

Hi.

I need to summarize table after UNION - data is coming from multiple sources.
So I was suggested to use GROUPBY and it works great, but only for one summarization (COUNTX).

If I add another summarization - SUMX, it is not working anymore.
Can you please suggest? Code is below.
Thank you.

Consol = 

var Monitorings = 
                
                SELECTCOLUMNS(QA,
                "Site",                     related('Global Master List'[Global Site]),
                "CalDate",                  related('Calendar'[Date].[Date]),
                "CallIDString",             [Call ID String],
                "NonCrit Score",            0,
                "QA Customer Critical",     0,
                "QA BusinessCrit Score",    0,               
                "ComplCrit Score",          0,
                "CallsForecasted",          0,
                "CallsOffered",             0,
                "CallsAnswered",            0,                  
                "CallsAnsweredSL",          0,
                "CallsAbandonedSL",         0)

var Quality = 
                SELECTCOLUMNS(QA,
                "Site",                related('Global Master List'[Global Site]),
                "CalDate",             related('Calendar'[Date].[Date]),
                "CallIDString",	       blank(),
                "NonCrit Score",        [NonCrit Score %],
                "QA Customer Critical", [QA Customer Critical %] ,
                "QA BusinessCrit Score",[QA Business Critical %] ,               
                "ComplCrit Score",      [ComplCrit Score %],
                "CallsForecasted",      0,
                "CallsOffered",         0,
                "CallsAnswered",        0,                  
                "CallsAnsweredSL",      0,
                "CallsAbandonedSL",     0
                )


var UnitedTable=UNION (Monitorings,Quality)

var UnitedTable2=summarize(UnitedTable,[Site],[CalDate],[CallIDString])

return groupby(UnitedTable, [Site], 
                            [CalDate], 
                            "MonitoringsNum",COUNTX(CURRENTGROUP(),[CallIDString]),
                            "ComplCriticalScore",sumx(CURRENTGROUP(),[ComplCrit Score %])             
)

Last SUMX is trhrowing error - "THE CALCULATE function cant be used in expression argument for the GROUPBY clause"

[tamerj1 · Accepted solution]

Hi @Yaro 
Please try

Consol =
VAR Monitorings =
    SELECTCOLUMNS (
        QA,
        "Site", RELATED ( 'Global Master List'[Global Site] ),
        "CalDate", RELATED ( 'Calendar'[Date].[Date] ),
        "CallIDString", [Call ID String],
        "NonCrit Score", 0,
        "QA Customer Critical", 0,
        "QA BusinessCrit Score", 0,
        "ComplCrit Score", 0,
        "CallsForecasted", 0,
        "CallsOffered", 0,
        "CallsAnswered", 0,
        "CallsAnsweredSL", 0,
        "CallsAbandonedSL", 0
    )
VAR Quality =
    SELECTCOLUMNS (
        QA,
        "Site", RELATED ( 'Global Master List'[Global Site] ),
        "CalDate", RELATED ( 'Calendar'[Date].[Date] ),
        "CallIDString", BLANK (),
        "NonCrit Score", [NonCrit Score %],
        "QA Customer Critical", [QA Customer Critical %],
        "QA BusinessCrit Score", [QA Business Critical %],
        "ComplCrit Score", [ComplCrit Score %],
        "CallsForecasted", 0,
        "CallsOffered", 0,
        "CallsAnswered", 0,
        "CallsAnsweredSL", 0,
        "CallsAbandonedSL", 0
    )
VAR UnitedTable =
    UNION ( Monitorings, Quality )
VAR UnitedTable2 =
    GROUPBY (
        UnitedTable,
        [Site],
        [CalDate],
        [CallIDString],
        "@ComplCrit Score", SUMX ( CURRENTGROUP (), [ComplCrit Score] )
    )
RETURN
    GROUPBY (
        UnitedTable,
        [Site],
        [CalDate],
        "MonitoringsNum", COUNTX ( CURRENTGROUP (), [CallIDString] ),
        "ComplCriticalScore", SUMX ( CURRENTGROUP (), [@ComplCrit Score] )
    )

[amitchandak]

@Yaro , make sure these are not measures [ComplCrit Score %], [QA Customer Critical %],

do the calculation in group by

[Yaro]

hi @amitchandak, sorry, can you please explain a bit more, I didnt get you