issue with SUMMARIZECOLUMNS in a measure

[arq52]

Good evening,

I have yearly forecasts per items

I want to allocate them at a week level, taking into account the working days and an allocation table per month.

Each item belongs to a department (Dpt) :

This department defines which percentages to use in order to allocate this yearly forecast at a month level :

I have a Calendar table that defines if each date is a working day or not :

the Data model :

In DAX studio, I have been able to build the temporary table needed by the calculations, because some weeks may belonging to different months :

EVALUATE
VAR Table_Year_Month_Week =
    FILTER (
        SUMMARIZECOLUMNS (
            Calendar[Year],
            Calendar[Month number],
            Calendar[YYCW],
            Items[Item],
            "Year_fcst",
                CALCULATE (
                    SUM ( 'YearlyForecasts'[Yearly Quantity] ),
                    TREATAS ( VALUES ( Calendar[Year] ), 'YearlyForecasts'[Year] )
                ),
            "Month_pct",
                VAR pct =
                    CALCULATE (
                        MIN ( 'AllocationPerMonthDpt'[pct] ),
                        TREATAS ( VALUES ( Items[Dpt] ), 'AllocationPerMonthDpt'[Dpt] ),
                        TREATAS ( VALUES ( Calendar[Year] ), 'AllocationPerMonthDpt'[Year] ),
                        TREATAS ( VALUES ( Calendar[Month number] ), 'AllocationPerMonthDpt'[Month number] )
                    )
                RETURN
                    IF ( ISBLANK ( pct ), 0, pct ),
            "Month_WD",
                CALCULATE (
                    SUM ( Calendar[working day] ),
                    FILTER (
                        ALL ( Calendar ),
                        Calendar[Year]
                            IN VALUES ( Calendar[Year] )
                                && Calendar[Month number] IN VALUES ( Calendar[Month number] )
                    )
                ),
            "Week_WD", CALCULATE ( SUM ( Calendar[working day] ) )
        ),
        Calendar[YYCW] = 2627
            && Items[Item] = "ItemA"
    )
VAR tot =
    SUMX ( Table_Year_Month_Week, [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD] ) 
RETURN
    Table_Year_Month_Week
//{tot}

I have read in this article (https://www.sqlbi.com/articles/summarizecolumns-best-practices/ ) that “SUMMARIZECOLUMNS … in 2025 can be used in measures”. So do I :

Weekly forecast =
VAR Table_Year_Month_Week =
        SUMMARIZECOLUMNS (
            Calendar[Year];
            Calendar[Month number];
            Calendar[YYCW];
            Items[Item];
            "Year_fcst";
                CALCULATE (
                    SUM ( 'YearlyForecasts'[Yearly Quantity] );
                    TREATAS ( VALUES ( Calendar[Year] ); 'YearlyForecasts'[Year] )
                );
            "Month_pct";
                VAR pct =
                    CALCULATE (
                        MIN ( 'AllocationPerMonthDpt'[pct] );
                        TREATAS ( VALUES ( Items[Dpt] ); 'AllocationPerMonthDpt'[Dpt] );
                        TREATAS ( VALUES ( Calendar[Year] ); 'AllocationPerMonthDpt'[Year] );
                        TREATAS ( VALUES ( Calendar[Month number] ); 'AllocationPerMonthDpt'[Month number] )
                    )
                RETURN
                    IF ( ISBLANK ( pct ); 0; pct );
            "Month_WD";
                CALCULATE (
                    SUM ( Calendar[working day] );
                    FILTER (
                        ALL ( Calendar );
                        Calendar[Year]
                            IN VALUES ( Calendar[Year] )
                                && Calendar[Month number] IN VALUES ( Calendar[Month number] )
                    )
                );
            "Week_WD"; CALCULATE ( SUM ( Calendar[working day] ) )
        )
VAR tot =
    SUMX ( Table_Year_Month_Week; [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD] ) 
RETURN
//    Table_Year_Month_Week
tot

But my measure don’t work.

I get an error message :

"MdxScript(Model) (7, 9) Calculation error in measure 'YearlyForecasts'[Weekly forecast]: SummarizeColumns() and AddMissingItems() may not be used in this context."

link to the excel file ForecastAllocation.xlsx 

How to modify my measure to avoid the error?

[arq52 · Accepted solution]

I've been able to solve my issue by adding CROSSJOIN  :

VAR Table_Year_Month_Week =
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE ( Calendar; Calendar[Year]; Calendar[Month number]; Calendar[YYCW] );
            VALUES ( Items[Item] )
        );
        "Year_fcst";
            VAR CurrentYear = Calendar[Year]
            VAR CurrentItem = Items[Item]
            RETURN
                CALCULATE (
                    SUM ( 'YearlyForecasts'[Yearly Quantity] );
                    'YearlyForecasts'[Year] = CurrentYear;
                    Items[Item] = CurrentItem
                );
        "Month_pct";
            VAR CurrentYear = Calendar[Year]
            VAR CurrentMonth = Calendar[Month number]
            VAR CurrentItem = Items[Item]
            VAR CurrentDpt =
                CALCULATE ( MIN ( Items[Dpt] ); Items[Item] = CurrentItem )
            VAR pct =
                CALCULATE (
                    MIN ( 'AllocationPerMonthDpt'[pct] );
                    'AllocationPerMonthDpt'[Dpt] = CurrentDpt;
                    'AllocationPerMonthDpt'[Year] = CurrentYear;
                    'AllocationPerMonthDpt'[Month number] = CurrentMonth
                )
            RETURN
                IF ( ISBLANK ( pct ); 0; pct );
        "Month_WD";
            VAR CurrentYear = Calendar[Year]
            VAR CurrentMonth = Calendar[Month number]
            RETURN
                CALCULATE (
                    SUM ( Calendar[working day] );
                    ALL ( Calendar );
                    Calendar[Year] = CurrentYear;
                    Calendar[Month number] = CurrentMonth
                );
        "Week_WD";
            VAR CurrentYear = Calendar[Year]
            VAR CurrentMonth = Calendar[Month number]
            VAR CurrentWeek = Calendar[YYCW]
            RETURN
                CALCULATE (
                    SUM ( Calendar[working day] );
                    ALL ( Calendar );
                    Calendar[Year] = CurrentYear;
                    Calendar[Month number] = CurrentMonth;
                    Calendar[YYCW] = CurrentWeek
                )
    )
VAR result=
    SUMX (
        Table_Year_Month_Week;
        [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD]
    )
RETURN
    result

Thanks for having tryed to help me.

[v-priyankata]

Hi @arq52 
It's great to hear that, and thank you for sharing the solution it will definitely be helpful for others facing the same issue. Please reach out to the community if you have any other questions; we're happy to assist you.

[arq52]

Good evening,
Back from holidays. It doesn't work as there is "dupplicate values" in these both tables. I've tried to add a new table with unique years, and connect it to my calendar and to my YearlyForecast tables ; it doesn't work too.

[v-priyankata]

Hi @arq52 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@FBergamaschi Thanks for your inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @arq52 

I wanted to check if you had the opportunity to review the information provided by user. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @arq52 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[FBergamaschi]

It is difficult to answer without the pbix to inspect better

So my suggestion is to go back to ADDCOLUMNS and SUMMARIZE, see if that works and then we shall see when you can sare the pbix via some cloud serivice, can you?

Weekly forecast =
VAR Table_Year_Month_Week =
ADDCOLUMNS(
SUMMARIZE (
YearlyForecasts;
Calendar[Year];
Calendar[Month number];
Calendar[YYCW];
Items[Item]
);
"Year_fcst";
CALCULATE (
SUM ( 'YearlyForecasts'[Yearly Quantity] );
TREATAS ( VALUES ( Calendar[Year] ); 'YearlyForecasts'[Year] )
);
"Month_pct";
VAR pct =
CALCULATE (
MIN ( 'AllocationPerMonthDpt'[pct] );
TREATAS ( VALUES ( Items[Dpt] ); 'AllocationPerMonthDpt'[Dpt] );
TREATAS ( VALUES ( Calendar[Year] ); 'AllocationPerMonthDpt'[Year] );
TREATAS ( VALUES ( Calendar[Month number] ); 'AllocationPerMonthDpt'[Month number] )
)
RETURN
IF ( ISBLANK ( pct ); 0; pct );
"Month_WD";
CALCULATE (
SUM ( Calendar[working day] );
FILTER (
ALL ( Calendar );
Calendar[Year]
IN VALUES ( Calendar[Year] )
&& Calendar[Month number] IN VALUES ( Calendar[Month number] )
)
);
"Week_WD"; CALCULATE ( SUM ( Calendar[working day] ) )
)
VAR tot =
SUMX ( Table_Year_Month_Week; [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD] )
RETURN
// Table_Year_Month_Week
tot

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[arq52]

I have try your measure, but it returns an error :
"Column YYCW in SUMMARIZE function was not found in the input table"

Good evening,
At the end of my first message, there was the link to my excel file with the issue.

In powerbi my measure work perfectly fine and gives the expected result : ForecastAllocation.pbix 

The issue seams to be specific to excel.

[FBergamaschi]

You need to connect the tables YearlyForecast with Calendar in order for my code to work

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI