Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Nested SUMMARIZE: how to access column added to inner SUMMARIZE

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?

 

  • johnt75's avatar
    johnt75
    4 years ago

    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

7 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.