Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
AlB
Super User
Super User

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  Datanaut

 

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.