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.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

Hi jakubalbrecht 

please try

Average weekly business won volume =
AVERAGEX (
SUMMARIZE (
FILTER (
BusinessCases,
BusinessCases[Status] = "Won"
&& BusinessCases[Date type] = "Closed"
),
Calendar[Week number],
"SummarizedResult", SUMX ( BusinessCases, BusinessCases[Ammount] )
),
[SummarizedResult] + 0
)

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

Hi 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 numberSummarizedResult
16000 + 0
34000 + 0
42000 + 0
Weekly average4000
  • 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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    jakubalbrecht 

    Average weekly business won volume =
    AVERAGEX (
    VALUES ( Calendar[Week number] ),
    CALCULATE (
    SUM ( BusinessCases[Ammount] ),
    FILTER (
    BusinessCases,
    BusinessCases[Status] = "Won"
    && BusinessCases[Date type] = "Closed"
    )
    ) + 0
    )

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

      Hello tamerj1 , thank you very much for the update. We are pretty close - for every single week the calculation works as expected (incl. 0 values instead of skipped rows) but the final average return the sum of the weekly values instead of average values.

      But modificated solution provided by AlexisOlson bellow (ommited FILTER function) works perpectly.

      Thank you very much to both of you!