Measure: Distinctcount per order and Customer. Sum and Average

[Anonymous]

I am trying to create a measure that calculates distinct count of colors ordered based on customer and order. See excel example below and desired results. 

[richbenmintz · Accepted solution]

Hi @Anonymous

Based on your requirement you can use the following 5 Measures which will provide the result in the image. Note the data must be formatted like you example data for the formulas to work as written 

base distinct count = DISTINCTCOUNT(Table1[Color])

Distinct Count Per Customer = 
var tab = SUMMARIZE(Table1,Table1[Customer], "orderid", [base distinct count])
return CALCULATE(SUMX(tab, [base distinct count]))

Distinct Count Per Order = 
var tab = SUMMARIZE(Table1,Table1[Order], "orderid", [base distinct count])
return CALCULATE(SUMX(tab, [base distinct count]))

Avg Distinct Count Per Customer = 
var tab = SUMMARIZE(Table1,Table1[Customer], "Cust", [base distinct count])
return CALCULATE(AVERAGEX(tab, [base distinct count]))

Avg Distinct Count Per Order = 
var tab = SUMMARIZE(Table1,Table1[Order], "cust", [base distinct count])
return CALCULATE(AVERAGEX(tab, [base distinct count]))