Problem with context calculation

[Former Member]

Dear all,

I'm facing another context calculation problem in BO.
I created a table with one calculated field as a Dimension.
At the end of this table, I'm summing up this field, counting the number of unique IDs and building the average based on these numbers:

This works perfectly and the calculation is correct. I put the formula for the "Avg days per ID" calculation directly in the cell:

=(Sum([DaysBetween])/Count([Reference Number];Distinct))

Now I want to display this very number (2.44) on a separate tab in this document. Every time I try to create this formula and save it as a variable and put it into any other place than at the bottom of this table it gives me a MULTIVALUE error.

How can I reference to this table to put the formula into context?

Thanks in advance for your help!

[Former Member]

Try Puting a MAX at the beginning of the formula =MAX(Sum([DaysBetween])/Count([Reference Number];Distinct))

[Former Member]

Unfortunately, still no success. I get a MULTIVALUE error with everything I try.

It's crazy, the right number is just below the table but I can't get it separated into a formula

Do you guys have any other idea how to do this?

[Former Member]

Thanks for your responses Amit, Niraj! Unfortunately, both do not work. When I create a variable (measure) with the formula it just displays MULTIVALUE.

Any other ideas?

[Former Member]

You can modify your formula like below if you do not have a block level filter.

=(Sum([DaysBetween]) In Report /Count([Reference Number];Distinct) In Report)

Regards

Niraj

[amitrathi239]

try like this.

=Sum([DaysBetween]) foreach ([Your First Date];[Second Date];[Reference Number])

check with this if your days sum is coming correct or not.Similar to this then first check count of reference number.