topic Re: Use SUMMARIZE in measure calculations in DAX Commands and Tips

Use SUMMARIZE in measure calculations

SajjadMousavi — Wed, 11 May 2022 13:50:18 GMT

Hi all.

I have created a calculated table as follows:

CompanyMSSummarized = SUMMARIZE(FILTER(DistributionPerCompany, DistributionPerCompany[ProdType] = "Disrtribution"), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM(DistributionPerCompany[RealQty]))

This table summarizes another table which has many columns, from which only the above 3 are needed. Then I have added these calculated columns to CompanyMSSummarized:

CurrentPct =

VAR TotalQty = CALCULATE(SUM(CompanyMSSummarized[Qty]), FILTER(ALL(CompanyMSSummarized), (CompanyMSSummarized[Year] = EARLIER(CompanyMSSummarized[Year]))))

RETURN 100 * CompanyMSSummarized[Qty] / TotalQty

PrevPct = CALCULATE(SUM(CompanyMSSummarized[CurrentPct]), FILTER(ALL(CompanyMSSummarized), (CompanyMSSummarized[Company] = EARLIER(CompanyMSSummarized[Company])) && (CompanyMSSummarized[Year] = EARLIER(CompanyMSSummarized[Year]) - 1)))

Now, I want to know if there is any way to re-write these columns as measures. I need this because measures have some formatting options which can be used in some visuals (such as KPIs), which are not available directly through those visuals. As these fields are not calculated very often (Once a month), using measures and in-memory calculations is not resource-intensive compared to calculated tables.

Thanks in advance

Re: Use SUMMARIZE in measure calculations

tamerj1 — Wed, 11 May 2022 14:22:42 GMT

Hi SajjadMousavi
Almost the same

CurrentPct = VAR TotalQty = CALCULATE ( SUM ( CompanyMSSummarized[Qty] ), FILTER ( ALL ( CompanyMSSummarized ), CompanyMSSummarized[Year] = EARLIER ( CompanyMSSummarized[Year] ) ) ) RETURN 100 * SUM ( CompanyMSSummarized[Qty] ) / TotalQty

PrevPct = CALCULATE ( SUM ( CompanyMSSummarized[CurrentPct] ), FILTER ( ALL ( CompanyMSSummarized ), CompanyMSSummarized[Company] = EARLIER ( CompanyMSSummarized[Company] ) && CompanyMSSummarized[Year] = EARLIER ( CompanyMSSummarized[Year] ) - 1 ) )

Re: Use SUMMARIZE in measure calculations

SajjadMousavi — Thu, 12 May 2022 06:10:52 GMT

Thansk for your reply, but I'm looking for a way to skip creation of summarized table. In addition, you cannot use EARLIER function in a measure, as measres are not aware of row context.

Re: Use SUMMARIZE in measure calculations

tamerj1 — Thu, 12 May 2022 06:41:59 GMT

SajjadMousavi
There is a row context created by FILTER. However, please clarify exactly what is required. I'm a little confused. Thank you

Re: Use SUMMARIZE in measure calculations

SajjadMousavi — Sat, 14 May 2022 04:06:18 GMT

I just want to skip creation of CompanyMSSummarized table in data model (want to create it on-the-fly in some way) and do calculations. But when I summarize DistributionPerCompany in a measure, I cannot use it in subsequent calculations (like the one you suggested) and Power BI gives this error:

Re: Use SUMMARIZE in measure calculations

tamerj1 — Sat, 14 May 2022 04:14:46 GMT

SajjadMousavi

Please paste the so I can copy it and edit

Re: Use SUMMARIZE in measure calculations

daXtreme — Sat, 14 May 2022 09:44:04 GMT

Hi SajjadMousavi

1. You absolutely can create a table on the fly, store it in a var and USE it subsequently. The only thing you're missing in your code is the correct references to the columns of the table. You cannot use the name of the variable to refer to them. For instance, you have to use SUMX and the original names of the columns. It's all about DATA LINEAGE.

2. You should never use SUMMARIZE to perform calculations in its body since SUMMARIZE is a very buggy (and complex) function (very dangerous in measures), and MS have refused fixing it a long time ago. Instead, you should use the hybrid SUMMARIZE/ADDCOLUMNS. Use SUMMARIZE only to get the distinct combination of rows from a (extended) table.

3. You should also never filter a full table (row 7 in your code). Always only the minimum number of columns that are needed.

4. EARLIER will not work in your code because to use it you have to have 2 different row contexts active at the same time. One is not enough. Incidentally, you should never use this function. Instead, you should use variables.

Best

Re: Use SUMMARIZE in measure calculations

SajjadMousavi — Sun, 15 May 2022 12:48:50 GMT

Thanks a lot for details. About 3rd point you mentioned, this table has exactly minimum number of columns needed for calculations. Summarization is just to sum up data per year (as can be seend from code). Anyway, I will try to re-write the code using guidelines you gave. Hope I can do it.

Re: Use SUMMARIZE in measure calculations

SajjadMousavi — Sun, 15 May 2022 12:53:11 GMT

Thanks in advance for your time:

CurrentPct = VAR CompanyMSS = SUMMARIZE(FILTER(DistributionPerCompany, DistributionPerCompany[ProdType] = "Distribution"), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM(DistributionPerCompany[RealQty])) VAR TotalQty = CALCULATE ( SUM ( CompanyMSS[Qty] ), FILTER ( ALL ( CompanyMSS ), CompanyMSS[Year] = EARLIER ( CompanyMSS[Year] ) ) ) RETURN 100 * SUM ( CompanyMSS[Qty] ) / TotalQty

Re: Use SUMMARIZE in measure calculations

tamerj1 — Sun, 15 May 2022 13:22:13 GMT

SajjadMousavi

Not sure what are you trying to achieve, but you may try

CurrentPct = VAR CurrentYear = MAX ( DistributionPerCompany[Year] ) VAR CurrentCompany = MAX ( DistributionPerCompany[Company] ) VAR CompanyMSS = SUMMARIZE ( FILTER ( ALLSELECTED ( DistributionPerCompany ), DistributionPerCompany[ProdType] = "Distribution" && DistributionPerCompany[Year] = CurrentYear ), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM ( DistributionPerCompany[RealQty] ) ) VAR TotalQty = SUMX ( CompanyMSS, [Qty] ) VAR Qty = SUMX ( FILTER ( CompanyMSS, DistributionPerCompany[Company] = CurrentCompany ), [Qty] ) RETURN 100 * Qty / TotalQty

Re: Use SUMMARIZE in measure calculations

SajjadMousavi — Mon, 06 Jun 2022 06:19:37 GMT

Thanks a lot. I was so busy that I couldn't check here. Will test your solution.