Total calculation based on dimension criteria shown on each row

[Finde]

Hi

This might be a very simple question - but after spending days trying out with aggr-formulas, SetAnalysisWizard combinations etc. I need to ask it:-) I have a straight table with a lot of dimensions and some expressions. I need an expression that on each row show the sum/total of the dimension "Quantity", but where  dimension B and dimension C are equal. In Excel that would easily be done with a sumifs-formula. Example of wanted output is shown in the last column in the table below. Can you help with an expression-formula to calculate that in QV?

The purpose is, after this, to divide the dimension Quantity, per row, with the calculated expression Quantity total.

Thanks, Finde

Dimension A	Dimension B	Dimension C	Quantity	Quantity total - with dimension B+C being equal
Horse	Half	Yes	2	14
Fish	Half	Yes	7	14
Bee	Full	Yes	4	4
Bee	Full	No	3	3
Bee	Half	Yes	5	14

[marcus_sommer · Accepted solution]

You may try it with: sum(total <[Dimension B], [Dimension C]> Quantity)

- Marcus

[Finde]

Hi Marcus. Thanks, that works. However, it sums up all the values from the dataset loaded into QV - and not only the values shown/selected in the QV-table. E.g. I have year 2019-2021 in the QV-table, but the formula sums up all the years in the dataset (that are many more years). Is there some way to adjust the formula so it totals only based on what is shown/selected in the table, and not all values from the dataset loaded into QV? 

[marcus_sommer]

The total within the above suggestion ignored all dimensions within the object unless the two specified one - and the expression is fully responsive against the selection state.

If you have multiple states in use within your application and/or your real expression contained any set analysis it might be that these selections are ignored. That's easily checked - just comment the set analysis or use a copy and remove it.

- Marcus