Average of distinct count of text values

[JohnS1968]

Hello there,

Noobie here, apologies in advance. I have a salesID column (text) from which I need to calculate the average of the distinct count per clientID, in order to get the average individual frequency of purchase per store, etc. What is best practice to do this avoiding calculated columns?

Thank you!

[Anonymous]

Hi @JohnS1968,

Can you please share some dummy data that keep raw data structure to test? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Rygaard]

Hello 🙂

The best thing ( i think) would be to move this calculation to the DataWareHouse, and have it do the sums on the "clint" table.  or even better an Aggrigrated table where you add store, item and then do a count 🙂

[amitchandak]

@JohnS1968 , Try like

Averagex(Values(Table[clientID]) , calculate(distinctcount(Table[SalesID])))

Averagex(summarize(Table, Table[clientID], "_1" , calculate(distinctcount(Table[SalesID])), [_1])