Calculated field to create a key-value pair and append it to the data

Q&A
,
1

I have a dataset which has KPIs and corresponding Values as two different columns with field names - kpi and val.
I need to perform summation of few of these KPIs to populate another KPI’s value. For example
kpi val
A 3.2
B 4.5
C 7.2
M 4.6
K 8.2

I need to compute kpi - D as a summation of the kpi A, B and C’s value and append it to the kpi column itself such that it looks like this

kpi val
A 3.2
B 4.5
C 7.2
D 14.9
M 4.6
K 8.2

I tried using calculated field where KPI D’s val is given by -

Name - kpi_d_val
sumif(avg({val}), in({{kpi}, [“A”, “B”, “C”]))

But I am not sure how to create a key value pair such that key is “D” and val is the above computed value. This key value pair further should be appended to the table as shown above.

Further I’ll have a line chart which has A, B, C, D, M, K as X axis and corresponding values as the Y axis.
Can you please help me how to satisfy this requirement?

Also few notes on my attempts -
I had a null value for a KPI with similar name already in the dataset which I tried to populate the value with D’s value like

ifelse({kpi}=“J”, kpi_d_val, avg({val}))

But this keeps giving mismatch aggregation error. I tried a lot of combinations but keep getting this error.

2

Keeps throwing this error -

Mismatched aggregation. Custom aggregations can’t contain both aggregate "`SUM`" and non-aggregated fields “SUM(CASE WHEN IN("kpi", _UTF16'KPI A', _UTF16'KPI B', _UTF16'KPI C', THEN "val" ELSE 0 END)”, in any combination.
3

Hi @tpanati
Thank your for your question. I am not sure this is possible right now, as you cannot append/union data within the dataset editor. Filling in of missing data would most likely have to be done at the database level in the original dataset. I will mark this as a feature request.