Average of Distinct Count, showing result in Total column only

[min-E]

Hello Community,

Two Part Question.

1) I am using the built in feature Count (distinct) of xid to populate the matrix below.

I would like to include the Average of Count (distinct) in the matrix.

The built-in average won't work in this case, since this isn't a number. 

2) Would it be possible to only show the Average of Count in the total columns? Don't think so, but perhaps someone knows for sure.

Link to a test copy.

Test Count and Average Count.pbix 

[Anonymous · Accepted solution]

Hi @min-E ,

For your first question, we can utilize MEASURE when the built-in COUNT does not apply.

If you want to make it possible to calculate the average in the total column, we need the help of two more measures.

DistinctCountMeasure = DISTINCTCOUNT(Sheet1[xid])

Once you have done this, you can see the average in the total column.

AverageDistinCount = AVERAGEX(VALUES(Sheet1[Region]),CALCULATE('Sheet1'[DistinctCountMeasure]) )

Display = IF(ISINSCOPE(Sheet1[Region]),[DistinctCountMeasure],[AverageDistinCount]) 

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @min-E ,

For your first question, we can utilize MEASURE when the built-in COUNT does not apply.

If you want to make it possible to calculate the average in the total column, we need the help of two more measures.

DistinctCountMeasure = DISTINCTCOUNT(Sheet1[xid])

Once you have done this, you can see the average in the total column.

AverageDistinCount = AVERAGEX(VALUES(Sheet1[Region]),CALCULATE('Sheet1'[DistinctCountMeasure]) )

Display = IF(ISINSCOPE(Sheet1[Region]),[DistinctCountMeasure],[AverageDistinCount]) 

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[min-E]

Hello! Thank you for taking the time to reply. It looks like the Display measure is showing the Count.

Maybe I am misunderstanding how that measure works.

Filtering down to 2 months below, as an example:

The Count of 43 is correct.

The Average should be 21.5 (43/2)

[Anonymous · Accepted solution]

Hi @min-E ，

I'm glad that my MEASURE helped you to solve part of your problem, seeing that your current requirement is that you want to average the Count of xid based on the date,

then you can try to use the following scenario, hopefully it will solve your problem.

Measure = 
VAR a = MAX(Sheet1[date])
VAR b = MIN(Sheet1[date])
VAR c = DATEDIFF(b,a,MONTH) + 1
RETURN
[DistinctCountMeasure]/c

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.