Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mq2020
Helper III
Helper III

CALCULATE AVERAGE OF DISTINCT COUNT

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)

 

sales table.PNGcustomer table.PNG

 

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.

 

desired output.PNG

 

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

can i have a bi file ? Please

 

AB

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.