Forum Discussion

jakubalbrecht's avatar
jakubalbrecht
Icon for Helper I rankHelper I
2 years ago
Solved

Avoid skipping empty rows when SUMMARIZE table with zero records in the source

Hello,   using DAX I am trying to execute following sequece: FILTER the records from table BusinessCases according to the conditions SUMMARIZE the filtered records by weeks (there is a relation ...
"DAX help"
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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
    _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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

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
    _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.