Forum Discussion

duybachhpvn's avatar
duybachhpvn
Frequent Visitor
4 years ago
Solved

SUMX/SUMMARIZE optimization

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's avatar
    amitchandak
    4 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      duybachhpvn
      Frequent Visitor
      4 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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