cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mk720x Member
Member

Measure that takes an average of counts

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Measure that takes an average of counts

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  Datanaut

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Measure that takes an average of counts

Hi @mk720x 

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  Datanaut

 

mk720x Member
Member

Re: Measure that takes an average of counts

@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.

Highlighted
Super User
Super User

Re: Measure that takes an average of counts

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  Datanaut

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 154 members 1,475 guests
Please welcome our newest community members: