Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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?

 

1 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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Super User
Super User

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
Not applicable

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

 

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
Not applicable

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

change it to

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

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

Anonymous
Not applicable

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

EylesIT_0-1659427734718.png

 

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:

EylesIT_2-1659428953548.png

 

Thanks very much for your help, @johnt75 .

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors