This is certainly possible.
Evaluating a dynamic expression
This is possible, as you can dynamically evaluate fields as expressions by using the $ sign expansion. EG $(xField) where the text in xField is '=field1 + field2' will evaluate to field1 + field2, instead of the text
There are several ways around this.. and it will depend on the size and structure of your data. Perhaps if you could post an idea of your data model I might be able to help more? Anyway, take your pick:
These first two assume that you have created the expression as a separate table (NOT joined to the other table). Hence when you select a ScoreName, just one expression is selected.
1)Change the expressions to pick out the values using set analysis
Change the expressions to read something like
Where the dimension is Vehicle, this will only sum the various scores needed in each score that you have selected.
2) pivot the score data in the load script
This will make the Expressions a lot simpler to write and execute, but might be ineffective for large data volumes or for lots of different types of ScoreNames.
Your ScoreData table would now look like
Vehicle EffScore CostScore RiskScore 1 .6 .5 .8 2 .4 .1 .8
If [EffScore],[CostScore] and [RiskScore] are now fields, the expression for TopLvlScore can now just remain '=EffScore+CostScore+RiskScore'
If the scores are as consistent as they appear, you could join the expression table to the Score Data table using the ScoreName:
3) Add ScoreName to the Expression table
This would join the expression table to the ScoreTable. The idea is that the expression table now has many rows for each expression, and when you select an expression it will select the subsequent scores, and only sum them. This does not require any dollar sign expansions:
Expression ScoreName TopLvlScore EffScore TopLvlScore RiskScore
When you select TopLvlScore, it will dynamically select just the Scores with EffScore and RiskScore in the ScoreName column of the other chart and so the expression in the chart will just need to be a sum of the Scores
Have I understood this correctly?!
Thanks for the reply Erica, I have attached a basic data model as you requested so you (and others) can take a look. Basically, we will only give scores to the very lowest level and the upper-level scores will be determined by the expressions in sheet1. This is a simplified version of the raw data as it will go down several additional levels in some of the categories. I appreciate any and all assistance!