topic SUMX/SUMMARIZE optimization in DAX Commands and Tips

SUMX/SUMMARIZE optimization

duybachhpvn — Wed, 09 Feb 2022 10:15:17 GMT

Hi,

Does anyone know how to fine tune the below DAX measure? I need to be able to sum the result of the spending based on fields from 3 Dimension tables: Cost_Categories, SiteName and AreaTypeName.... to do this, I have to combine SUMX and SUMMARIZE 3 times which is very inefficient. Looking for ideas to achieve the result without the need to replicate the SUMX/SUMMARIZE combination 3 times

CALCULATE( SUMX( SUMMARIZE( SiteName, SiteName[SiteName], "Site Cost", SUMX( SUMMARIZE( AreaTypeName, AreaTypeName[AreaTypeName], "Cost", SUMX( SUMMARIZE( Cost_Categories, Cost_Categories[CostCategoryName], "Spends", SUM(Actual[Cost]) - SUM(Budget[Cost]) ), [Spends] ) ), [Cost] ) ), [Site Cost] ), NOT(Cost_Categories[CostCategoryName] IN {"Subcon","Material", "Controller"}) )

Re: SUMX/SUMMARIZE optimization

amitchandak — Wed, 09 Feb 2022 10:13:11 GMT

duybachhpvn , It is possible that you take actual fact in summarize and add all dimension columns

CALCULATE(
SUMX(
Addcolumns( SUMMARIZE(
Actual,
SiteName[SiteName],
AreaTypeName[AreaTypeName],
Cost_Categories,
Cost_Categories[CostCategoryName],

),
"Spends", SUM(Actual[Cost])
), [Spends])
NOT(Cost_Categories[CostCategoryName] IN {"Subcon","Material", "Controller"})
)

Re: SUMX/SUMMARIZE optimization

duybachhpvn — Wed, 09 Feb 2022 10:19:01 GMT

Hi amitchandak , sorry I simplified my example, I have updated it. The calculation is actually from Actual and Budget tables, therefore I cannot put the Actual Fact in the SUMMARIZE. Do you know how to do in this case?

"Spends", SUM(Actual[Cost]) - SUM(Budget[Cost])

Re: SUMX/SUMMARIZE optimization

Anonymous — Tue, 22 Feb 2022 09:59:14 GMT

Hi duybachhpvn ，

In order to provide you a suitable solution, could you please share some sample data in the table Cost_Categories, SiteName , AreaTypeName, Actual and Budget? Is there any relationship created among these tables? What's your final expected result? Please provide the calculation logic and special examples screenshot. Thank you.

Best Regards