Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
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"})
)
Solved! Go to 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 , 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"})
)
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])
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
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |