Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sum of Distinct ID's not calculating as needed

Posted on 2008-06-11
2
Medium Priority
?
937 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 2000 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Integration Management Part 2
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

577 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