topic Re: issue with SUMMARIZECOLUMNS in a measure in DAX Commands and Tips

issue with SUMMARIZECOLUMNS in a measure

arq52 — Wed, 27 Aug 2025 21:37:15 GMT

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?

Re: issue with SUMMARIZECOLUMNS in a measure

FBergamaschi — Thu, 28 Aug 2025 08:31:39 GMT

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

Re: issue with SUMMARIZECOLUMNS in a measure

arq52 — Thu, 28 Aug 2025 21:03:12 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

FBergamaschi — Fri, 29 Aug 2025 11:45:03 GMT

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

Re: issue with SUMMARIZECOLUMNS in a measure

v-priyankata — Tue, 02 Sep 2025 11:04:43 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

v-priyankata — Fri, 05 Sep 2025 06:36:44 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

v-priyankata — Wed, 10 Sep 2025 05:58:31 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

arq52 — Wed, 17 Sep 2025 21:12:53 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

arq52 — Sun, 21 Sep 2025 15:47:21 GMT

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.

Re: issue with SUMMARIZECOLUMNS in a measure

v-priyankata — Mon, 22 Sep 2025 08:41:44 GMT

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.