Gettiing Average of Unique Product sold per month with DistinctCount

[shekhar_shres]

Hi all, 

I hope you are all doing well. I have a very common SalesTable (fact table) that has a ProductID column. To calculate distinct product sold, I am using following Dax.

Unique Product Sold = DistinctCount(SalesData[ProductID]) and I am getting following results in my matrix table 

Jan 2023	Feb 2023	Mar 2023	Total
1,725	3,823	4,299	4,989

So the unique product sold for Jan, Feb and March is 1725, 3823, 4299 respectively but the unique product sold in Quarter 1 is 4,989. I want to show averages in the Total column, that is (1725+3823+4,299)/3 = 3282.33.

I applied the new DAX Average = Divide(DistinctCount(SalesData[ProductID], DistinctCount(dimDate[MonthInCalendar]), but the result I am getting is 1663, that is 4989 (Unique product sold in Q1)/ 3 (DistinctMonth)

Jan 2023	Feb 2023	Mar 2023	Total
1,725	3,823	4,299	1,663

I can see why it is doing that but I can't seem to be able to calculate the averages the way I want. 

Is there a way I can achieve following result:

Jan 2023	Feb 2023	Mar 2023	Total
1,725	3,823	4,299	3,282

Please let me know if I have been unclear. 

Thanks in advance.

Shekhar

[Greg_Deckler · Accepted solution]

@shekhar_shres This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.