cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kokumo Frequent Visitor
Frequent Visitor

Measure: Distinctcount per order and Customer. Sum and Average

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. 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

Re: Measure: Distinctcount per order and Customer. Sum and Average

Hi @Kokumo

 

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 

 

Distinct Counts.PNG

 

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

 

 

1 REPLY 1
richbenmintz
Advisor

Re: Measure: Distinctcount per order and Customer. Sum and Average

Hi @Kokumo

 

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 

 

Distinct Counts.PNG

 

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