Percentile over Group Totals

[Mujahid.muqtar]

Hi Everyone,



I'm trying to group data in a report from single table using a field in grouping and sorting and I want the percentile of every record over group total. For this currently I'm using a query to fetch data from table, however I'm unable to get percentage of every single record over group total.
I want to display the report as below tables in single report. I'm unable to get data in "Perc" field. It's populating wrong values. Thanks in Advance

Group1

Item	Cost	Perc
Traffic Detours	1,160,000	4.95%
General Utilities	2,175,000.00	9.31%
Road and Asphalt Works	20,065,028.00	85.74%
Total	23,400,028	100%

Group2

Item	Cost	Perc
Material	2,410,843.00	30.44%
Subcontractor	5,510,123.00	69.56%
Total	7,920,966.00	100%

[aytee111]

Create a first query that only calculates the total. Then use this total query (unjoined) in the query above to calculate the percent.

[Mujahid.muqtar]

The problem is solved, I Used Dsum function in "Perc" field as

[Cost]/dsum("[Cost"]"[Query]","[Group]"='" & [textgroup] & "'")).

Thanks anyway. Thank you Aytee111, I appreciate your reply.

[aytee111]

Yes, that will work, but for future reference avoid "D" functions whenever possible, they are the most inefficient way of getting data. Fine for having only a few records in your table(s).