Nested SUMMARIZE: how to access column added to inner SUMMARIZE

[Anonymous]

I cannot seem to get nested SUMMARIZE working.

I have this measure below, which is working correctly. It give me a flag called _Missing, at (Org, Month) level, which indicates if any sales data is missing for that month.

MyMeasure = 
        SUMX(
            CALCULATETABLE(
                ADDCOLUMNS(
                    CROSSJOIN(VALUES('Organisation'[OrgCode]), VALUES('DimDate'[YYYYMM]))
                    ,"_Missing", IF(ISBLANK([Sales]), 1, 0)
                    ,"_OrdQty", [OrdQty]
                    ,"_OrdValue", [OrdQty]
                )
                ,ALL('DimDate')
            )
            ,[_Missing]
        )

 Now what I want to do is get the sum of that _Missing flag at Org level. My intension is for my measure to only returns a value for those orgs that have no missing months i.e. have _Missing = 0 at (Org) level.

I tried this measure below:

MyMeasure =
        SUMX(
            SUMMARIZE(
                CALCULATETABLE(
                    ADDCOLUMNS(
                        CROSSJOIN(VALUES('Organisation'[OrgCode]), VALUES('DimDate'[YYYYMM]))
                        ,"_Missing", IF(ISBLANK([Sales]), 1, 0)
                        ,"_OrdQty", [OrdQty]
                        ,"_OrdValue", [OrdQty]
                    )
                    ,ALL('DimDate')
                )
                ,'Organisation'[OrgCode]
                ,"_MissingOrgLevel", SUM([_Missing])
            )
            ,[_MissingOrgLevel]
        )

but it generates this error:

'_MissingOrgLevel' cannot be found or may not be used in this expression.

Any ideas how to achieve this?

[Anonymous]

I have cracked it. I need to create a second measure:

MissingMonths = 
    SUMX(
        CALCULATETABLE (
            ADDCOLUMNS (
                CROSSJOIN ( VALUES ( 'Org'[Code] ), VALUES ( 'DimDate'[YYYYMM])),
                "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 )
            ),
            ALL ( 'DimDate' ),
        )
        ,[_Missing]
    )

and then for the Org level, the measure is:

MyMeasure =
    SUMX(
        VALUES ( 'Org'[Code] )
        , IF([MissingMonths] = 0, [Sales])
    )

This returns the total Sales for only those Orgs that do not have any missing months.

[johnt75]

try

MyMeasure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE (
                ADDCOLUMNS (
                    CROSSJOIN ( VALUES ( 'Organisation'[OrgCode] ), VALUES ( 'DimDate'[YYYYMM] ) ),
                    "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 ),
                    "_OrdQty", [OrdQty],
                    "_OrdValue", [OrdQty]
                ),
                ALL ( 'DimDate' )
            ),
            'Organisation'[OrgCode]
        ),
        "_MissingOrgLevel", SUM ( [_Missing] )
    ),
    [_MissingOrgLevel]
)

[Anonymous]

@johnt75 thanks for the suggerstion, but unfortunately it gives a similar error, this time for the calculated column [_Missing]:

Column '_Missing' cannot be found or may not be used in this expression.

[johnt75 · Accepted solution]

Storing the table in a variable may help

MyMeasure =
VAR generatedTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( 'Organisation'[OrgCode] ), VALUES ( 'DimDate'[YYYYMM] ) ),
            "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 ),
            "_OrdQty", [OrdQty],
            "_OrdValue", [OrdQty]
        ),
        ALL ( 'DimDate' )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ),
            "_MissingOrgLevel", SUMX ( generatedTable, [_Missing] )
        ),
        [_MissingOrgLevel]
    )

BTW, both _OrdQty and _OrdValue use the same measure

[Anonymous]

Thanks  , that does allow me to access the [_MissingOrgLevel] column, which is what I was after. 

But something is not working correctly. The line

"_MissingOrgLevel", SUMX ( generatedTable, [_Missing] )

does not seem to honour the OrgCode; the line seems to always give the sum of [_Missing] for the entire generatedTable, for every row of the SUMMARIZE.

For example, when I select just one OrgCode which has missing months, then the measure returns 1 as expected. And when I select just onw OrgCode which had zero missing months, then the measure returns 0 as expected.

But when I select both of those two OrgCodes, I want the measure to return 1; but it returns 2.

And if I select 9 OrgCodes that have zero missing months plus one OrgCode that does have missing months, it returns 10.

@johnt75

[johnt75]

change it to

"_MissingOrgLevel", CALCULATE( SUMX ( generatedTable, [_Missing] ) )

that will force it to pick up the org during context transition

[Anonymous]

@johnt75 , wrapping it in a CALCULATE did not work. I tried this in the second half of the measure

    RETURN
        CONCATENATEX( 
            SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ),
            'Organisation'[OrgCode] & "(" & CALCULATE(SUMX(generatedTable, [_Missing])) & ")",
            ","
        )

and the results when I select three Orgs (0702, 0703, 0140) are below. Notice how the Total row shows all three Orgs with the same SUM of 26 for _Missing.

I have got it working by using a FILTER on the table variable like this:

    RETURN
        CONCATENATEX( 
            SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ),
            VAR vOrgCode = 'Organisation'[OrgCode]
            RETURN
              'Organisation'[OrgCode] & "(" & SUMX(FILTER(generatedTable, 'Organisation'[OrgCode] = vOrgCode), [_Missing]) & ")",
            ","
        )

which gives these correct results:

Thanks very much for your help, @johnt75 .