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
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)
Thanks,
Tomas
Solved! Go to Solution.
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
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
Thank you, highly appreciated!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |