Reply
SteveG_91
Helper I
Helper I
Partially syndicated - Outbound

Calculating An Average Of A Measure

I have a table named ITC_DAILY_STATS that records one row for each application received.  Our months are divided into one of five categories, named month_code (12WD, 12WE, 3WD, 8WD, 8WE).  I'm trying to calculate the average number of apps received each month, grouped by the month_code.  In other words, what is the average number of apps received in the 12WD months, in the 12WE months, etc.  I have a slicer so the user can select one of the month_codes.

 

I have this measure to count the number of apps received, which works fine

App Count = count('ITC_DAILY_STATS'[APP_NUM])
 
Following other posts on here I wrote this measure to calculate the average of the 'App Count' measure
APP COUNT AVG = AVERAGEX(VALUES('A2055 ITC_BIZ_DATES'[Month Code]),'Calculations'[APP COUNT])
 
However, when I display the APP COUNT AVG in a card visual it gives me the total of all the months in that month_code rather than the average.  In this example below, the total is 147,276 but what I want is the average of that (36,819).
 
SteveG_91_0-1655927512552.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@SteveG_91 , Can try it on month year name

APP COUNT AVG = AVERAGEX(VALUES('A2055 ITC_BIZ_DATES'[Month Year name]),calculate([APP COUNT]) )

 

or

 

APP COUNT AVG = AVERAGEX(summarize('A2055 ITC_BIZ_DATES','A2055 ITC_BIZ_DATES'[Month Year name],[Month Code]),calculate([APP COUNT]) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@SteveG_91 , Can try it on month year name

APP COUNT AVG = AVERAGEX(VALUES('A2055 ITC_BIZ_DATES'[Month Year name]),calculate([APP COUNT]) )

 

or

 

APP COUNT AVG = AVERAGEX(summarize('A2055 ITC_BIZ_DATES','A2055 ITC_BIZ_DATES'[Month Year name],[Month Code]),calculate([APP COUNT]) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Syndicated - Outbound

Thanks @amitchandak , your first measure worked, APP COUNT AVG = AVERAGEX(VALUES('A2055 ITC_BIZ_DATES'[Month Year name]),calculate([APP COUNT]) ).

 

I thought I had used month year name on my first try and it didn't work so I switched to month_code.  I guess I didn't.  Anyway, thanks for your help!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)