Having looked at your example the calculation looks like its working [Rappel Calculado] seems to be multiplying the [Total Liquido] by the correct Parameter value for Group A, B and C (and not D as there is no parameter). There seem to be multiple [CodMarca] values to [Marcas] but only one that ever has [Total Liquido] values. The Grand Totals look ok too.
If I remove the [Marcas] value the aggregation to the groups look ok ?
What result do you want to get?
This is to calculate the amount of discount by group of brand (A, B and C). Each group as a different %.
So I have to IF the several brands to say in what group they belong.
In the end I want to SUM([Total Liquido]) + SUM([Rappel Ano Anterior]) and like this it turns in a Aggregated field.
Thank you once again.
I have found a solution but know I have a Grand Total Problem: It's not right. Sub Totals work well but not Grand Totals.
The solution is:
(INT( MIN ([CodMarca])) = 10129 or
INT( MIN ([CodMarca])) = 97129 or
INT( MIN ([CodMarca])) = 10130 or
INT( MIN ([CodMarca])) = 10147 or
INT( MIN ([CodMarca])) = 10152 or
INT( MIN ([CodMarca])) = 10153 or
INT( MIN ([CodMarca])) = 10154 or
INT( MIN ([CodMarca])) = 10158 or
INT( MIN ([CodMarca])) = 10160 or
INT( MIN ([CodMarca])) = 10161 )
[Valor Total Liq.]*[% Grupo A]
[Valor Total Liq.] it's a aggregated.
1 of 1 people found this helpful
It is important to know that grand totals are not necessarily are sum of the values in the rows/columns. Rather, they use whatever the aggregation is on the measure. So for example, if max is the aggregation being used on a measure, the grand total will be the largest value in the column/row, not a sum of the max values. This is a great suggestion for the idea board--allowing the user to decide how the grand total should be aggregated.
So I need to get back to the Beginning of my problem:
I Want to do IF with some Aggregate and some none Aggreagate:
IF [PRODUCT] = "10101" or [PRODUCT]= "10102" THEN SUM(something) * [ParameterA]
ELSE IF [PRODUCT] = "10103" or [PRODUCT]= "10104" THEN SUM(something) * [ParameterB]
How can I do That in a Calculated Field?
Thank you very Much
Have you tried something like:
sum(IF [Product] = 10101 or [Product] = 10102 then [Something] * [ParameterA] else [Something] * [ParameterB] end )
Thank you, but it doesn't work.
I Tried MIN(INT([Product] = 10101)) ...
and with SUM, MAX, ATTR and it works on some Products (not all) but in Grand Totals the results aren't correct.
With regards to your Grand Totals the grand total uses the same aggregation as in your calculated field. For example if you use a MIN aggregation the grand total will show the MIN of your selection, if you use a MAX it'll show the MAX, if you choose SUM it'll sum the selection, etc.
It doesn't make sense that what you're seeing works for some products but not for others. Perhaps if you attach the workbook and a brief description of what you're doing and where it fails it'll help with finding a solution.
Before sending this I just had a read higher up the thread and realised Tracy said the exact same thing about the grand totals.