cancel
Showing results for
Did you mean:
Kokumo 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. 1 ACCEPTED SOLUTION

Accepted Solutions

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

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