Total issue

[mahitham]

Hi Experts,

Can any one please help me on below issue.

In the below table I have calculated CC1 measure using below expression.

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

CC1 total is 167,947,700 but getting Auto total as 120,672,758

The requirement is  I have to create the second table in the app with Name and Scope as dimension and CC1 as Measure but getting all wrong totals.

.

Please help me on this.

Thanks in advance.

[mahitham]

Hi,

Could you please help me on above issue.

stalwar1

jyothish8807

youssefbelloum

[marinadorcassio1]

Hi Mahitha,

The total CC1 gives you bad result because the total isn't the sum the different values found for CC1, but is recalculated on the total data.

It does :

RangeMax(0,Sum(Value)) +

Sum(Value) here =

48906566

RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

Sum(Item1) here =

16410074

Sum(Item2) here =

28795310

etc...

Then does RangeMax on Sum(Item1), Sum(Item2)... /5 + 0,1, here =

71766192,1

Then sum 48906566 and 71766192,1, which =

120672758

You should try aggregating by the choosen dimensions.

Hope it helps,

Marina

[ChennaiahNallani]

replace null and missing values with Zero '0" and Try

[rubenmarin · Accepted solution]

Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:

Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1, Date, Name, SCOPE))

In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)