Calculate Average of Distinct count values by summarizing columns(Need Measure or Column not table)

[Anonymous]

Hello Team,

I need your suggestions in  creating Average value for Distinct count of ID's after summarizing three columns in a table.

ID	Category	Code	Date	Subject	Type
1	A	X	7/20/2020	ABC	Sales
1	A	Y	7/21/2020	ABC	Sales
2	B	Z	7/20/2020	DEF	Purchase
3	B	Z	7/22/2020	DEF	order
4	B	Z	7/23/2020	XYZ	order

What I need is to Create Calculated column/ measure which gets Average of distinct count of ID column after summarizing Category, code, date, subject columns.

Please suggest me dax code for this,

Thank you,

[amitchandak]

@Anonymous , depending on the need

averageX(summarize(Table,Table[Category],Table[code],Table[date], Table[Subject],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[Category],Table[code],Table[date],"_1",distinctCOUNT(Table[ID])),[_1])

averageX(summarize(Table,Table[Category],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[code],Table[date],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[date], Table[Subject],"_1",distinctCOUNT(Table[ID])),[_1])

[Anonymous]

Thank you @amitchandak 

I am not getting expected result when I apply this solution to my report

Thanks you

[harshnathani]

Hi @Anonymous ,

Can you share the expected output ?

Regards,

Harsh Nathani

[mahoneypat]

Please try this in a measure expression

Avg ID Count =
AVERAGEX (
SUMMARIZE ( Table, Table[Category], Table[Code], Table[Date], Table[Subject] ),
CALCULATE ( DISTINCTCOUNT ( Table[ID] ) )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat]

You can use this formula to get your results

ID DistinctCount = CALCULATE(Distinctcount(Table[ID]), ALLEXCEPT(Table, Table[ID], Table[Type]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thank you @mahoneypat .

But I need to calculate average(Distinctcount(Table[ID])) after summarizing above mentioned columns.