I have a distinct count measure that shows proper subtotal but incorrect
Grand Total. For example:

Region Office SubscriptionCount
RegionA Office1 3
Office2 5
RegionA Total 7
RegionB Office1 2
Office2 3
RegionB Total 5
Grand Total 57 (instead of 12)

Note: There are more offices in each region, however, I selected only a
couple from each region. If I select all the offices the Grand Total is
correct.

Any advice would be greatly appreciated
Thanks,
Miroslav

do you have any other members?
can you send the MDX query?
the result appear to not be a visualtotal, its why the total is different.
(grand total = All Region * All Offices; visual total will evaluate only the
totals for the selected members)

also, do you use a many-many relationship? (AS2005 only)

"Miroslav" <miroslav.brkic (AT) ca (DOT) fujitsu.com> wrote

Hi Jéjé,

Thanks for your prompt reply.
My cube should be a very simple one. I just have one fact table and three
dimensions linked to the fact table using the dimension keys. I created the
subscription count measure by selecting "New measure" and then selecting
Usage: Distinct count
Source Table: My Fact table
Source Column: I selected the dimension key
When I browse the cube using Development studion I am getting incorrect
result.
I do not know where to find MDX for the measure created. I captured MDX
executed during the browsing using profiler but it is a huge statement and
you probably do not want to see that.
How and where should I specify that I want to see visualtotal?
Thanks,
Miroslav

"Jéjé" wrote:

do you use AS2000 or 2005?
you talk about profiler, so I presume you use AS2005.

the visualtotal is an MDX function, you have to call it in your MDX syntax.
to see how to use it, use the profiler to intercept the query generated by
the pivottable.
just start the profiler before you do a move in the pivottable.
the pivottable use the visualtotal by default

you can find some infor on the help about the visualtotal function.

"Miroslav" <miroslav.brkic (AT) ca (DOT) fujitsu.com> wrote

I found the following link related to visual total:
http://doc.ddart.net/mssql/sql2000/h...tions_8l0z.htm
That article says:
"
Important: The VisualTotals function cannot be used in a query to a cube
that contains a distinct count measure; such a query will return an error for
all measure values.
"
It looks like I will have to find another way. I will do more research and
post any interesting finding.

Thanks,
Miroslav

"Jéjé" wrote:

yes, for AS2000
you have to create a dynamic distinct count measure (or calculated DCount
measure)
this measure is slower but support the visual totals

something like:
count(crossjoin(<dimension>.<level>.members, {measures.[AnyCountOrSum
measure]}))

search on the web for more detail and information for these distinct count
measures. there is some articles.


"Miroslav" <miroslav.brkic (AT) ca (DOT) fujitsu.com> wrote

can you use the Visual Totals function with a calculated member?

([Measures].[Total Sales Amount] - [Measures].[Total Sales Credited]) -
[Measures].[Discount Amount]

After removing the distinct count from the cubes, using the visualtotal
option worked for the Excel grand total; however, I found a couple of
discrepancies with a few of my calculated members in Excel but not sure why.

For instance, the calculated member mdx above is reporting $1,104,832.38 for
the sales rep under a particular sales mgr, but the correct amount should be
$1,074,261.33. So excel is reporting a extra $30K but unsure why. I thought
if I explicitly use the Visual Total function in the calculated member it
would resolve the problem.

Any help?

"Jéjé" wrote: