topic Calculate Average of Distinct count values by summarizing columns(Need Measure or Column not table) in DAX Commands and Tips

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

Anonymous — Wed, 29 Jul 2020 01:27:57 GMT

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,

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

mahoneypat — Wed, 29 Jul 2020 01:38:17 GMT

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

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

Anonymous — Wed, 29 Jul 2020 01:46:13 GMT

Thank you mahoneypat .

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

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

mahoneypat — Wed, 29 Jul 2020 01:56:47 GMT

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

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

amitchandak — Wed, 29 Jul 2020 02:44:35 GMT

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])

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

Anonymous — Mon, 03 Aug 2020 10:48:58 GMT

Thank you amitchandak

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

Thanks you

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

Anonymous — Tue, 04 Aug 2020 09:10:47 GMT

Hi Anonymous ,

Can you share the expected output ?

Regards,

Harsh Nathani