how to redistribute rows into other rows

[shivamkedia]

Table1:

columnname1 Columnname2

v                                     1

w                                   2

x                                    4

y                                    8

z                                  30

Suppose i have Filterpane of  Dimension columnname1, if i click on  Z or filter Z, then  columnname2 corresponding column z will be redistributed to other rows in their respective ratios and add with their respective columns.

eg suppose I filter  on Z then  result will be like

columnname1 Columnname2    colmnname3

v                                     1                          (  (1/(1+2+4+8))*30)+1=3

w                                   2                            (  (2/(1+2+4+8))*30)+2=6

x                                    4                            (  (4/(1+2+4+8))*30)+4=12

y                                    8                            (  (8/(1+2+4+8))*30)+8=24

remove z if only z is filtered ,if 2 columns are selected then sum ofcolumnname2  corresponding to filterd columns will redistributed into the remaining.

eg:

if i filtered y and z then 38 will reditributed in their remaining rows w.r.t their ratios.

[kuba_michalik]

You need a formula like this for the final column (I took the liberty of shortening the names of columns in the data...):

Sum({<col1=1::col1-$::col1>} col2) + (Sum({<col1=1::col1-$::col1>} col2) / Sum({<col1=1::col1-$::col1>} total col2)) * Sum(total col2)

Instead of this funky "col1=1::col1-$::col1" syntax you could use the element function E() and "col1=E(col1)" which looks nicer, but can be potentially influenced by selections in other fields than just col1.

If you need any explanation on how this works let me know 😉