Forum Discussion

ed_mcdill's avatar
ed_mcdill
Advocate II
9 years ago
Solved

Nested Sumx performance

Project Management Cost_Variable_Target =

 

VAR TargetPercentage =
ADDCOLUMNS (
Projects,
"Target_Percent", SWITCH (
TRUE (),
Projects[Project_Type] = "Type A", 0.125,
Projects[Project_Type] = "Type B", 0.11,
Projects[Project_Type] = "Type C", 0.12,
Projects[Project_Type] = "Type D", 0.06,
Projects[Project_Type] = "Type E", 0.107,
0
)
)

 

RETURN
SUMX (
TaskForecastMonths,
SUMX ( TargetPercentage, [Target_Percent] * [Project_Cost] )
)


I have Project dimension table (Projects) and a fact table (TaskForecastMonths) which stores how much the project costs by month. This is a tabular model owned by another team so I can't add a calculated column.

 

Based on the type of project I assign a different percentage (this is the bit I'd maybe use a calculated column if I owned the model), this percentage I then multiply against the project cost in the TaskForecastMonths fact table. This gives me a target cost for a team that I use for a KPI.

 

This works but it is very slow, (15-20 seconds to filter) and I'm assuming a nested sumx might be the issue. Does anyone have any experience with this? Also what's the best practice for submitting questions like this, how should I format and frame the question?

 

Thanks for all the help I've been receiving on this form.

3 Replies