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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tktom
Frequent Visitor

Average sales excluding blank days

Hello everyone,

 

with the calculation below I was able to calculate the average daily sales as seen on the screen below, but I would need to adjust my calculation to exclude blank/zero days. Could you please help? The expected result is average of the two values in column "Net Sales".

 

Original calculation:

Daily AVG = 
VAR selectedDate = SELECTEDVALUE('Date'[Date])

VAR Res = SELECTEDVALUE(Restaurant[Restaurant Name]) 

RETURN

CALCULATE(SUM('Daily Sales Report'[Net Sales]); MONTH('Date'[Date])=MONTH(selectedDate); YEAR('Date'[Date])=YEAR(selectedDate); Restaurant[Restaurant Name]= Res)/CALCULATE(DISTINCTCOUNT('Date'[Date]);MONTH('Date'[Date])=MONTH(selectedDate); YEAR('Date'[Date])=YEAR(selectedDate); Restaurant[Restaurant Name]= Res;'Daily Sales Report'[Net Sales]>0)

tktom_0-1648216767852.png

 

Thanks,

 

Tomas

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Daily Avg =
var selectedDate = SELECTEDVALUE('Date'[Year month])
var result = AVERAGEX( ADDCOLUMNS( 
   CALCULATETABLE( VALUES( 'Date'[Date]), REMOVEFILTERS('Date'), 
      'Date'[Year month] = SELECTEDVALUE('Date'[Year month]) ),
   "@val", CALCULATE( [Net Sales] ),
[@val])
return result

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

Daily Avg =
var selectedDate = SELECTEDVALUE('Date'[Year month])
var result = AVERAGEX( ADDCOLUMNS( 
   CALCULATETABLE( VALUES( 'Date'[Date]), REMOVEFILTERS('Date'), 
      'Date'[Year month] = SELECTEDVALUE('Date'[Year month]) ),
   "@val", CALCULATE( [Net Sales] ),
[@val])
return result
tktom
Frequent Visitor

Thank you, highly appreciated!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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