Solved

Sum of Distinct ID's not calculating as needed

Posted on 2008-06-11
2
925 Views
Last Modified: 2013-11-16
I have run across an issue that seems so simple but I cannot figure it out.  I have multiple rows from a detail table that consist of a number that I need to summarize. I only want to calculate one of the numbers from each ID but everything that I have tried either will not summarize the formula or it totals all rows.

RefID      Number
1731720      586
1731720      586
1731720      586
1731720      586
      
1731411      586
1731411      586
1731411      586
      
1732298      600
1732298      600
1732298      600

Total      5,902

The total that I am trying to get is 1,772 from the above example which consist of each ID and the number that goes with it.  I cannot do a distinct row because there are other columns within the  rows that do not have distinct values (TimeDate, Actions, etc)

The I cannot use distinct row counts because not all columns within the report are the same. How can I calculate this correctly?
0
Comment
Question by:aelliso3
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 21761512
Use a RunningTotals field.

Sum the number field and use 'On change of field' set to the refid.
0
 
LVL 11

Author Closing Comment

by:aelliso3
ID: 31466209
Excellent ... That worked perfectly ...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question