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 AreaTypeNa...
  • 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"})
    )

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