Say I have a chart as follows
Apr Mar
1 1
1 2
1 3
2 4
3 5
Distinct Count 3 5 Total 5
How can I get a distinct count by Month as the Total? I want the result for the Total to be 8, 3 from Apr and 5 in Mar.
I am using this expression
Count(Distinct Numbers)
What I am trying to do is get an average but I have not been able to get the avg and aggr function to work so I am trying to create the averages myself.
I also tried using set analysis to get all of the months Distinct counts and then divide by the total Month/Year combinations as follows.
=((count({$<[Backlog Month] = {"Jan"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Feb"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Mar"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Apr"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"May"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Jun"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Jul"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Aug"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Sep"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Oct"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Nov"}>}Distinct [Claim Number]) +
(count({$<[Backlog Month] = {"Dec"}>}Distinct [Claim Number])) /
TextCount(Distinct [Backlog Month/Year Combo])
The reason I cant get this to work is that if you select the Backlog Month as Apr, it does not react because the SET is for all months.
So, I would like the second scenario to work properly when selecting [Backlog Month] or I will need a solution for the first scenario to do a distinct Count by Backlog Month.
Does anyone have any ideas. Thanks in advance......Troy
