Calculate different counter from the same dimension iteratively

What could be the suggested way to calculate KPI from in a single dimension for example:

Object value
A 100
B 200
C 120
D 220

Expected result

From To Diff
A B 100
A C 20
A D 120
B C 80


We have not planned to calculate this in dataframe as we expect user to select e.g. source. Or the another case, select both source and target from filter and find pair.

What could be the function the we could use to achieve this calculation?
Please advise. Thank you very much.

@Stima_Livingpresent you can use sum-if statements to accomplish this, here’s an example expression:

sum(if(object='A',value,0)) - sum(if(object='B',value,0))
1 Like

How could we create column From And To, it comes from the same dimension (Object Column). If we use object in the self, so we can have 2 parameters like object1 and object2. but how it can be used as dimension. I have tried use it in case but still cannot make it work. For the measure, I am not sure if we can use sum if as object ‘A’ or ‘B’ can be anything e.g. from selection from filter shelf or the value is changed based on dimension (object ) value.

@Stima_Livingpresent If it was just a single pair then it would be possible to setup.

To dynamically create all pair combinations is not possible in the UI.

If you have many pair combinations that are dynamic then it’s best to generate that set in your back-end data. And then simply filter in the UI to the subset you are interested in.

Agreed. Will go for data set then. Thanks.