CALCULATE AVERAGE OF DISTINCT COUNT

[mq2020]

Morning,

I am trying to create some measures to display the average of unique products purchased by each customer based on customer characteristics.

I have one table with sales wich includes daily sales on customer and products, and another table with unique customers and their characteristics (i.e active customer Y/N, custoerm logged on last 90 days Y/N, etc)

The relationship of the tables in the model is Many to 1 (Sales to Customer)

The output I am looking for is something like below where I can recalculate on average how many distinct products are purchased by customers in a specifc category.

I already have a measure to calcualte the number of customers for each funnel category but now I need a measure for the average of distinct product purchased.

I started creating something like this but I got stuck in the RETURN part, I am not sure what syntax I should use:

Av Product purchased by Customer Logged Last 90 days=

var count_table =

SUMMARIZE('Sales','Sales'[ClientId], "Products", CALCULATE(DISTINCTCOUNT('Sales'[Product])))

RETURN

CALCULATE(AVERAGE('count_table'[ProductLine]),FILTER('Customer',[Logged Last 90 days]="Y"))

Help please? Thanks

[amitchandak · Accepted solution]

@mq2020 , it should come like

AVERAGEx(SUMMARIZE('Sales','Sales'[ClientId], "_Products", CALCULATE(DISTINCTCOUNT('Sales'[Product]))),[_Products])

the filter should be part of either table name or Calculation of measure

AVERAGEx(SUMMARIZE('Sales','Sales'[ClientId], "_Products", CALCULATE(DISTINCTCOUNT('Sales'[Product]))),[_Products])

[Amardeep100115]

can i have a bi file ? Please

[mq2020]

@amitchandak , that was super helpful, thank you for saving my day!