error summing distinct count measures in Excel

[SuperChris]

I have the same exact problem that another user on this
site had so I am going to use his description. From what
I can see the question was never answered. I can not figure
out how to total vaules that are produced using the count()
function in the cube. Here is an exelent discription of the problem. We have this problem here at Blue Cross Blue Sheild with all of our cubes, a solution would be greatly appreciated. OLD Thread # 960-839077

I am not using any explicit MDX. I have built a cube with a measure using the derived distinct count aggregation. I then publish the cube to a web server and access it with Excel. The distinct count aggregations on the total calculation displays a #VALUE! error.

Example:

Bagels 10
Muffins 20
Bread 30
Total #VALUE!

Column1 is product, Column2 is a distinct count measure.
The total is a grand total (should be 60).
This also occurs when I select multiple members in a dimension on the slicer.

If you select ALL Members it works fine and if you select
ONE Member it works fine. You only get the error when you select 2 or more members and it trys to total them.

Thanks for any help.
Chris Smith

 

[hmortada]

I have the same problem toooooooooo

 

[MDXer]

It seems to me that this is more of a problem within excel than it is with Analysis services.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SuperChris]

does this post to the problem I wrote

 

[SuperChris]

No the problem is with Anal Serv. I found documentation discussing the limitation. What I had to do to get around the issue was create another level in my dimension. For the above example I would create a bread product level that contains bagels, muffins and bread. Now when I select bread products I get the totals. Kind of a hassel but its working for me.

 

[Flybridge]

Where did you find the documentation? Was it in the on-line books or another web site?
Might be a useful link for us all.

Thanks,

Steve

40 million lemmings can't be wrong........can they?

 

[AllenNelson]

I have been having the same problem and have an idea. I have a distinct count measure in my cube and when I go to validate the data in Excel I am also getting the #Value! error when selecting individual members where I do not get the error if I choose All.

Explanation of data setup:
I have a fact table with invoice lines. The invoice number may be duplicated in the table due to multiple lines on the invoice. There is a customerid on each line in the fact table. That customerid points to another table that contains market, region, and company information. I use this table for several dimensions.

I wanted to know how many invoices had been paid within a region. If I go into excel and attempt to get a count of distinct invoice numbers for a particular region I get the error if I choose anything but 'All'; however, it works perfectly if I let it stay on 'All'.

I took the time to move the region directly into the fact table instead of letting the cube join to the foreign table and now it works. Im not sure why distinct count doesnt work when you are using a dimension with members from a foreign table but in my situation that seems to be what the problem was.

I hope that helps :S

 

[AllenNelson]

OK, I am sorry. What I just posted is not the case. My test was not complete. It in fact did not work. I cannot seem to make a distinct count measure work when selecting multiple members of a dimension. Sorry for the false post.