Forum Discussion
Helper IAvoid skipping empty rows when SUMMARIZE table with zero records in the source
I would start with a full set of weeks and then calculate amount along these lines:
Average weekly business won volume = VAR _FullWeeks_ = VALUES ( Calendar[Week number] ) VAR _Avg = AVERAGEX ( _FullWeeks_, CALCULATE ( SUM ( BusinessCases[Ammount] ), BusinessCases[Status] = "Won", BusinessCases[Date type] = "Closed" ) + 0 ) RETURN _AvgYou might need something more sophisticated for _FullWeeks_ as I'm not sure how the min and max week numbers are determined in your scenario.
Helper IHi tamerj1 ,
thank you for your suggestion. These were my first ideas of the solution too, but unfortunately this does not work 😞
Suggested solution adds 0 to every single row generated by the SUMMARIZE function, but unfortunately does not force SUMMARIZE function to avoid ommiting rows for weeks with no records.
When I use my example, the suggested solution results in following - week number 2 still missing:
| Week number | SummarizedResult |
| 1 | 6000 + 0 |
| 3 | 4000 + 0 |
| 4 | 2000 + 0 |
| Weekly average | 4000 |
Super UserI would start with a full set of weeks and then calculate amount along these lines:
Average weekly business won volume =
VAR _FullWeeks_ = VALUES ( Calendar[Week number] )
VAR _Avg =
AVERAGEX (
_FullWeeks_,
CALCULATE (
SUM ( BusinessCases[Ammount] ),
BusinessCases[Status] = "Won",
BusinessCases[Date type] = "Closed"
) + 0
)
RETURN
_Avg
You might need something more sophisticated for _FullWeeks_ as I'm not sure how the min and max week numbers are determined in your scenario.
- jakubalbrecht
Thank you, AlexisOlson , works perfectly.