6 Replies Latest reply on Feb 21, 2014 7:24 AM by Matt Lutton

# How to get a summed measure of the distinct count of a dimension?

Hi all,

I have an excel file in which we have multiple dimensions with the same measures that I need some help with getting an accurate sum.

Essentially, I want to do a distinct count against the Grouping column and then get a sum rolled up against that distinct count. Using the below example, I only want to sum the # of Members once for each group and return that value. Using that logic and the below data set the sum I’m looking for would be “20” as opposed to “35”. I have attached a packaged workbook which already includes an extract in case a Distinct Count needs to be used.

-Eric

• ###### 1. Re: How to get a summed measure of the distinct count of a dimension?

The data in your workbook does not match the excel file--but you can get what you want by simply dividing the # of members by the number of records.

Here you go:

1) Create a calculated field: SUM(# of Members)/SUM(Number of Records)

Use that field instead of # of members, and add the grand total.

Done.

• ###### 2. Re: How to get a summed measure of the distinct count of a dimension?

Matthew,

Hmm...Sorry about that not sure what happened there. Either way, thanks a lot! Such a simple solution! Much appreciated sir

• ###### 3. Re: Re: How to get a summed measure of the distinct count of a dimension?

Matt-

I just noticed something -- I created that table calculation and then put the total on there -- while each of the groups show up correctly, the Grand Total isn't giving me the "20" rather it's returning 4.375 -- do you know why this is??

• ###### 4. Re: How to get a summed measure of the distinct count of a dimension?

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

Right click on the True Sum pill on the text shelf and choose "Total Using>>Sum" (forgot to mention that before)

There's a lot of info on how grand totals are computed at the link above; study that, and you'll get an idea of what's going on.

1 of 1 people found this helpful
• ###### 5. Re: Re: How to get a summed measure of the distinct count of a dimension?

Ahhh, and there it is! Thanks again -- I'll check out that link and see what more info I can learn. Thanks again sir!

• ###### 6. Re: Re: How to get a summed measure of the distinct count of a dimension?

No problem!  They added the ability to change the total aggregation in 8.1, which makes life a lot easier in cases like this.  Cheers!