cancel
Showing results for 
Search instead for 
Did you mean: 

Sum DistinctCount and percentage

Former Member
0 Kudos

Hi,

I am trying to to calculate the percentage on each line of this example...

               Units     Percent

Office 1     10     45,45%

Office 2     12     54,55%

Each line "Office x" is a group where units are a distinctcount of a field.

I need to get the percentage respect the sum of units column. Note that it is not the same sum units value of each line

that a global distinctcount for all records.

I don't know how to get the sum of 10+12 in each line to calculate the percentage.

Thank you,

Francesc

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Francesc,

You would need a Subreport to get the sum of the distinct counts to further calculate the percentage for each group.

Here's how:

1) Open the existing report

2) Go to Insert Subreport > Click Choose an existing report and open the same report again

3) Place the Subreport on the Report Header

4) Double click the subreport so that you can see its design view

5) Create a formula with this code and place this on the Group Header:

shared numbervar perc := perc + DistinctCount({Units}, {field_used_as_grouping});

6) Suppress ALL sections on this subreport

7) Right-click the subreport > format > subreport tab > check 'suppress blank subreport'

😎 Go to the section expert > highlight the report header > check 'Suppress blank section'

9) Create a formula with this code and place this on the Group Header beside the distinct count field:

shared numbervar perc;

DistinctCount({Units}, {field_used_as_grouping}) % perc;


Hope this helps.


-Abhilash

Former Member
0 Kudos

Great!!

Thank you Abhilash

Francesc.

Answers (0)