Gettiing Average of Unique Product sold per month with DistinctCount

shekhar_shres — Mon, 18 Mar 2024 22:58:35 GMT

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

Re: Gettiing Average of Unique Product sold per month with DistinctCount

Greg_Deckler — Mon, 18 Mar 2024 23:07:06 GMT

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.

topic Gettiing Average of Unique Product sold per month with DistinctCount in DAX Commands and Tips

Gettiing Average of Unique Product sold per month with DistinctCount

Re: Gettiing Average of Unique Product sold per month with DistinctCount