Average - Distinct Count

[spandy34]

I have the matrix below  which has a Distinct Count of field [Claim Ref] within the Table named INS_Main Claim Data by Calendar Month.

I would like a column at the end after March column which has the Average for each year for each Class of Business

How would I write the DAX for this please?

@amitchandak @tamerj1 @danextian @goncalogeraldes @v-tangjie-msft @v-kaiyue-msft @Uzi2019 

[v-yaningy-msft]

Hi, @spandy34 

Here, thanks for @lbendlin reply. You can refer to his reply, and if it didn't work, you can share the pbix file without sensitive data, or share the datasheet, measure, etc. that will enable you to simulate your matrix.

Best Regards,
Yang
Community Support Team

[spandy34]

Calendar Table

Date	CalMonth	CalYear	Dateid
03/07/1899	Jul	1899	18990703
08/07/1901	Jul	1901	19010708
07/07/1902	Jul	1902	19020707
06/07/1903	Jul	1903	19030706
04/07/1904	Jul	1904	19040704
03/07/1905	Jul	1905	19050703
08/07/1907	Jul	1907	19070708
06/07/1908	Jul	1908	19080706
05/07/1909	Jul	1909	19090705
04/07/1910	Jul	1910	19100704
03/07/1911	Jul	1911	19110703
08/07/1912	Jul	1912	19120708
07/07/1913	Jul	1913	19130707
06/07/1914	Jul	1914	19140706
05/07/1915	Jul	1915	19150705

[lbendlin]

[spandy34]

Hi

Thank you for the pbix file.

I need an Average Column next to the Total field so will have the following

To include

                Total     Average

EL          17          1.54                     17/ 11 months) = 1.54

IN           5             0.45

ML         25          2.27

MV         5             0.45

OT          5             0.45

PL          6             0.54

PR          4             0.36

Total     67          6.09

[lbendlin]

[spandy34]

Below I have a column called z_Average with the following.  At the moment it is dividing distinct count of the Claim Ref, by the distinct count of the month year.  How do it amend it so it divides by the count of the month_year which in the example should be 3 (April 2024, June 2024 and May 2024) as opposed to 1 month.

z_Average = DIVIDE(DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef], DISTINCTCOUNT('INS_Fin_Calendar Main Claim Data Notification Date'[month_year]))

[lbendlin]

You will have to define what to do with months that you have selected but that have no data. Should they be considered for the average or not?

[spandy34]

No they should not be considered only the months with data 

[lbendlin]

Personally I don't agree with that methodology.  Maybe someone else can help you further.

[spandy34 · Accepted solution]

Hi

I resolved this by

• Creating a column in the INS_Main Claim Data table and called it [AverageCountColumn] which placed a number 1 in each row of the table
• Within the same table I created a field called Month_Year which was the Notification Date in the format of

 Notification Month Year = FORMAT([NotificationDate]," mmm yyyy")

• I created a measure which calculated a distinctcount of the notification month year:-

Z_Count of Month_Year = (DISTINCTCOUNT('INS_Main Claim Data'[Notification Month Year]))

• I created a measure of the count of the total claim refs:-

z_Generic_No of Claims Distinct = DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef])

• I created an average measure

z_1Average = DIVIDE([z_Generic_No of Claims Distinct],[Z_Count of Month_Year])

• I then created the matrix

• I created the matrix and hid all the average fields apart from the average total

Using a slicer I can now create the a table below which calculates the average based on the number of distinct month year values.  I am sure there is a cleverer way to do this using DAX but with my limited knowledge this solution has worked.

[spandy34]

The months in the matrix are governed by a slicer so depending how many months are selected will determine how many months the average is divided by so we cant hard code it to 11 as you state in 

Average = divide(COUNTROWS('Table'),11,0)

for example below when I select 2024-25 financial year we only have April to June .  I think its difficult putting an average column in a matrix

[spandy34]

Hi  

Here is the data table linked to a calendar table by Dateid_NotificationDate

Claim Ref	ClassOfBusinessCode	NotificationDate	Dateid_NotificationDate
7313	IN	23/11/1999	19991123
7395	IN	03/04/2000	20000403
13029	IN	15/12/2004	20041215
12922	IN	24/10/2004	20041024
12921	IN	23/10/2004	20041023
25967	EL	20/03/2012	20120320
25010	EL	10/05/2011	20110510
25009	EL	10/05/2011	20110510
25008	EL	10/05/2011	20110510
25007	EL	10/05/2011	20110510
25006	EL	10/05/2011	20110510
24897	EL	04/04/2011	20110404
24877	EL	25/03/2011	20110325
24862	EL	22/03/2011	20110322
24844	EL	21/03/2011	20110321
24843	EL	21/03/2011	20110321
24842	EL	21/03/2011	20110321
24791	EL	08/03/2011	20110308
24790	EL	08/03/2011	20110308
24723	EL	15/02/2011	20110215
24599	EL	21/01/2011	20110121
24598	EL	21/01/2011	20110121
24511	ML	23/12/2010	20101223
24510	ML	23/12/2010	20101223
24477	ML	14/12/2010	20101214
24476	ML	14/12/2010	20101214
24474	ML	14/12/2010	20101214
24473	ML	14/12/2010	20101214
24472	ML	14/12/2010	20101214
24471	ML	14/12/2010	20101214
24350	ML	10/11/2010	20101110
24284	ML	26/10/2010	20101026
24249	ML	18/10/2010	20101018
24191	ML	28/09/2010	20100928
24190	ML	28/09/2010	20100928
24177	ML	24/09/2010	20100924
24176	ML	24/09/2010	20100924
24125	ML	15/09/2010	20100915
23957	ML	21/07/2010	20100721
23956	ML	21/07/2010	20100721
23883	ML	07/07/2010	20100707
23879	ML	07/07/2010	20100707
23878	ML	07/07/2010	20100707
23876	ML	07/07/2010	20100707
23875	ML	07/07/2010	20100707
23791	ML	22/06/2010	20100622
23790	ML	22/06/2010	20100622
10942	MV	03/02/2003	20030203
10122	MV	18/09/2002	20020918
9870	MV	23/07/2002	20020723
19447	MV	22/04/2008	20080422
18774	MV	28/01/2008	20080128
18057	OT	19/09/2007	20070919
18358	OT	15/11/2007	20071115
17210	OT	12/07/2007	20070712
17282	OT	23/07/2007	20070723
26217	OT	29/05/2012	20120529
26216	PL	29/05/2012	20120529
26215	PL	29/05/2012	20120529
26214	PL	29/05/2012	20120529
26213	PL	29/05/2012	20120529
26212	PL	29/05/2012	20120529
26295	PL	29/06/2012	20120629
26294	PR	29/06/2012	20120629
26293	PR	29/06/2012	20120629
26292	PR	29/06/2012	20120629
26273	PR	22/06/2012	20120622

[lbendlin]

no need for DAX.  Specify the value aggregation as Average, and enable Column totals.

[spandy34]

Hi  When you say specify the value aggregation as Average and enable Columns, Im a bit unsure how to do it.  Please can you suggest how I change the format below.  Many thanks

The Matrix is made up of

Rows  -               Class of Business Code

                                Year

Columns           CalMonth

Values                Count of Claims