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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MuthalibAbdul
Helper I
Helper I

Averages to include blank rows

Hello Community,

So to start with, I have a table with my employees and list of customers they dealt with on a day to day basis. I am able to get the count by day/week/month/quarter/year in a perfect way by having a Calender table and linking these two tables. 

 

Problem is when I calculate averages per month or per week, it does not include rows where 0 customers were seen. Hence the averages are higher than they should be. 

 

I have attached the dax below (created by quick measure) where I calculate the average per month and week. I have an interactive  dashboard and I also need the values to change when a selection has been made. Like a particular employee etc. 

 

Your help would be appreciated.

 

MuthalibAbdul_0-1717522591324.pngMuthalibAbdul_1-1717522610108.png

 

Update:

I just solved this by creating two measures, one for distinct count of week and other toal encounters and also tried your way. The results for both the method are same. However, your method is easy and quick. 

The next issue I have is few employees started in the middle of the year, say June. Both the methods mentioned above is calculating all the weeks since Jan. 

 

How do I set a conditional saying if any week has an input (basically means new employee joined), then start doing rolling average from that point forward. 

 

I am also anticipating another issue where an employee left, so provide a condition for a stop for particular employees. 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@MuthalibAbdul Try adding + 0 right after your CALCULATE statement



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@MuthalibAbdul Try adding + 0 right after your CALCULATE statement



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Perfect, I just solved this by creating two measures, one for distinct count of week and other toal encounters and also tried your way. The results for both the method are same. However, your method is easy and quick. 

The next issue I have is few employees started in the middle of the year, say June. Both the methods mentioned above is calculating all the weeks since Jan. 

 

How do I set a conditional saying if any week has an input (basically means new employee joined), then start doing rolling average from that point forward. 

 

I am also anticipating another issue where an employee left, so provide a condition for a stop for particular employees. 

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! Prices go up Feb. 11th.

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.

Jan NL Carousel

Fabric Community Update - January 2025

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