Subtotal based on different rows in a balance schema

[AB-mx]

I need to calculate the subtotals based on a dimension = at certain row line...

here the screenshot of what i mean....

RowNr	Description	Rif	Value
10	Sales	0
20	Direct sales	70	110
30	Wholesale	70	120
40	E-Commerce	70	150
50	Europe	70	150
60	America	140	200
70	Total Sales	130	Here need the total of the rows only with column rif= 70
80	Expenses	0
90	Material	120	85
100	Production	120	300
110	Administrative	120	250
120	Total Expenses	130	Here need the total of the rows only with column rif= 120
130	TOTAL Net		Here need the total of the rows only with column rif=130

please help asap

[StarinieriG]

Hi,

you could create one expression for each row (see file attached)

[AB-mx]

Thank you,

this could be an idea, but the problem is that the Schema is dynamic, and i can not omit the first column, the client in the system can add rows in the schema when he needs , if i follow your example i will need to add a new expression for the new row everytime he does change ...

Consider even that the total rows in the final schema (balance UE) are hundreds ...

i dont know if i've been clear enough

Any suggestion?

[AB-mx]

Any suggestions? please....

Thank you in advance

[sunny_talwar · Accepted solution]

You can try to use an expression like this

=Pick(Match(RowNr, 70, 120, 130) + 1, Sum(Value), Sum(TOTAL {<Rif = {70}>}Value), Sum(TOTAL {<Rif = {120}>}Value), Sum(TOTAL {<Rif = {70, 120}>}Value))

 

Note: In an effort to save time, I have used the sample posted by @StarinieriG (Thank you).

Best,
Sunny

[AB-mx]

Thank you so much, it seems perfect ... exactly what i need.