Measure that takes an average of counts

[Anonymous]

Hi!

I'm trying to create a measure that will render a count of index by category.

For example, let's pretend I have data structured as below:

Index| Category | Day

1 | A | Monday

2 | B | Monday

3 | B | Tuesday

4 | A | Tuesday

5 | B | Monday

I want to plot the average count per day by category. For example the example, the average count per category on Monday would be 1.5 and the average count per category on Tuesday would be 1.
 

Monday

A - 1 count

B - 2 counts

(Average 1.5)

Tuesday

A- 1 count

B - 1 count

(Average 1)

Help!

[AlB]

Hi @Anonymous 

1. Place Day in the rows on a table visual 

2. Place this measure in the visual

Measure =
AVERAGEX (
    DISTINCT ( Table1[Category] ),
    CALCULATE ( COUNT ( Table1[Index] ) )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

@AlB This is very helpful!

I am running into something else... perhaps I posed the question incorrectly, I apologize.
Ultimately I want to identify when a certain category's average exceeds the average of the total.

If I add a slicer for category, how to I keep one measure as an overall count of all of the data while comparing it against the filtered data.

For example, if I adjust the slicer to just look at Mondays, I want the measure to still show the overall average of counts.

[AlB · Accepted solution]

It's not completely clear but try this new measure that uses the one we wrote earlier:

AvgAcrossDays =
AVERAGEX ( ALL ( Table1[Day] ), [Measure] )

This will give you the average across all days (1,25 in the example you show) irrespective of the selection in the slicer

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers