topic Re: Getting an average of distinct items for a category/column in DAX Commands and Tips

Getting an average of distinct items for a category/column

yorick1973 — Tue, 04 Jun 2024 14:21:20 GMT

How do I get an average for the total of each distinct Item on a list?

Item Name	Category	Age
A	Class X	2
B	Class Y	10
C	Class Z	5
D	Class X	6
E	Class Y	2
A	Class X	4

When I do an average of Age by Category, I get the following:

Class X = 4 ((2+6+4)/3 Items)
Class Y = 6 (10+2)/2 Items)
Class Z = 5 (5/1 Item)

I need to show the average per distinct Item. If an Item has more than one entry, then the total of that Item is to be counted as one entry. The desired result would be this:
Class X = 6 ((2+6+4)/2 Distinct Items)
Class Y = 6 (10+2)/2 Items)
Class Z = 5 (5/1 Item)

How do I achieve that?

Re: Getting an average of distinct items for a category/column

Moetazzahran — Tue, 04 Jun 2024 21:42:27 GMT

Measure =

CALCULATE(SUM('Table'[Age])/DISTINCTCOUNT('Table'[Item Name]),ALL('Table'),'Table'[Category]=SELECTEDVALUE('Table'[Category]))

If the solutions answers your problem, pls mark my post as a solution and your Kudos is much appreciated!

Re: Getting an average of distinct items for a category/column

qqqqqwwwweeerrr — Wed, 05 Jun 2024 13:39:03 GMT

Hi yorick1973

This can be achived by using this dax:

average_ = DIVIDE(SUM('Table'[Age]),CALCULATE(DISTINCTCOUNT('Table'[Item Name]),ALLEXCEPT('Table','Table'[Category])))
this waht i am getting as an output

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards