formula(MinString) used in SUM() in table take in the global data set instead of local

[#1]

Hi All,

In Chart Table, I create a table like the following

Dimension	Expression	Expression
ScenarioNo	=MinString(myColumnName)	SUM($(=MinString(myColumnName)))
1	Column003	89,404,258
2	Column052	89,404,258
3	Column018	89,404,258

There is a one to one mapping between ScenarioNo and myColumnName, picking the ScenarioNo will choose the Column to sum up. Values from different columns (e.g. Column003 / Column052 / Column018) have different sum up value.

I'm not sure which part went wrong, although =MinString(myColumnName) does return the corresponding column name, SUM($(=MinString(myColumnName))) always return the same value which indicates =MinString(myColumnName) within SUM() always return the global min instead of the local min for each ScenarioNo.

Anyone have any idea why this happen and how can I achieve the actual column sum up to each scenario?

i.e. SUM(Column003) for ScenarioNo = 1, SUM(Column052) for ScenarioNo = 2, SUM(Column018) for ScenarioNo = 3 for each row

instead of SUM(Column000) for all three Scenarios.

Thanks very much!

Crystal

[Gysbert_Wassenaar · Accepted solution]

The dollar expansion $(=MinString(myColumnName)) is done outside the chart context. That's why you always get the sum of Column000. What you can try instead is a pick-match combination:

pick(match(myColumnName, $(=concat(chr(39)&myColumnName&chr(39),','))),

$(=concat('sum('[' &myColumnName&'])',',')))

[#3]

Hi Gysbert, thanks a lot for your advise, it solves my problem! That's a tricky way.

May I ask why the dollar expansion evaluation is done outside the chart context?