SUMX/SUMMARIZE optimization

[duybachhpvn]

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"})
    )

[amitchandak · Accepted solution]

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

like

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"})
)

[duybachhpvn]

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])

[v-yiruan-msft]

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