Sum of the result of a field and measure

[Drece-gkn]

Hello, 

I have a column in a table chart that is the calculation of [Total Sales] * [Handshake %] (Those are two other columns in the table as well)

I want to have the Sum of the column at the top, but I haven't been able to figure out the equation to solve this due to the fact that [Total Sales] is a measure and [Handshake%] is a field. 

I will show some of my attempts to get this to work.

//=Sum('Total Sales'*'Handshake %')

//Aggr(Sum([Total Sales]*[Handshake %]), [Material Number])
//RangeSum([Total Sales]*[Handshake %])
//Sum(Aggr(count({<[Handshake Scrap $ c =(a*b)]={1}>} distinct [Document Number]),[Handshake Scrap $ c =(a*b)]))

[LRuCelver]

Close to the end of the options for your measure should be a drop down for "Totals function".

Selecting Sum will display the sum of all rows at the top right under the header.

Just make sure totals are enabled:

[Drece-gkn]

I do not see these options for me. 

[Oleg_Troyansky · Accepted solution]

It's important to understand WHY this formula doesn't work at the total level, and then the solution will appear by itself.

In the detailed lines, there is a single value of the field [Handshake %], hence everything works well even without any aggregation functions (by default, the function ONLY() is being used).

In the total line, multiple values of [Handshake %] are available, and hence the result is NULL.

In order to make it work, you need to "open up" the formula for Total Sales and include the multiplication on the Handshake % within the aggregation. For example, if Total Sales = sum(Sales), then the correct formula should be:

sum(Sales* [Handshake %])

It's important that the Handshake % is included within the aggregation function and not outside of it.

If the formula for Total Sales is more complex, then you might be better off using the AGGR90 function, but then you still need to "open up" the formula for sales, and also remember to include ALL chart dimensions in the list of AGGR dimensions. Most importantly, the combination of all AGGR dimensions should always return a single value for the [Handshake %], otherwise this wouldn't work.
For example:

sum(AGGR( <complex formula for sales> * [Handshake %], Material, Document, Dim1, Dim2, Dim3...))

I hope this helps,

For example:

[vinieme12 · Accepted solution]

As below

SUM ( Aggr([Total Sales]*[Handshake %], ChartDimension1,ChartDimension2 ) )

[Drece-gkn]

Thanks for the help! Why is adding all chart dimensions important as well?

[Oleg_Troyansky]

Glad to help! To understand the full depth of AGGR(), I'd invite you to my lecture on Set Analysis and AGGR at the Masters Summit for Qlik.

In a nutshell, it's important that the aggregated data produced by AGGR() remains more granular (or at least as granular) as the chart dimensions, otherwise you will get unexpected results.

Cheers,