Average - Distinct Count

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.