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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jakubalbrecht
Regular Visitor

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

Hello,

 

using DAX I am trying to execute following sequece:

  1. FILTER the records from table BusinessCases according to the conditions
  2. SUMMARIZE the filtered records by weeks (there is a relation between SalesCases[Date] and Calendar[Date], Calendar[Week number])
  3. Calculate AVERAGE of the SUMMARIZED weekly sums

I have following DAX Measure:

 

 

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]
)

 

 

The measure works fine when there are records in the BusinessCases table meeting the filtering conditions.

 

But when there are no records meeting the conditions, the result of the SUMMARIZE function does not contain the specific week number at all which distorts the result of the AVERAGEX function then.

 

Example: assuming in week number 2 there are no records in BusinessCases table meeting conditions

  • current behaviour:
    Week numberSummarizedResult
    16000
    34000
    42000
    Weekly average4000

 

  • desired behaviour:
    Week numberSummarizedResult
    16000
    20
    34000
    42000
    Weekly average3000

Could you please help me to adjust the DAX formula above to change the behaviour that instead of skipping the weeks with empty records input, the 0 value for the specific week is returned to enable proper AVERAGE calculation?

 

Thank you

 

Jakub Albrecht

1 ACCEPTED SOLUTION

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.

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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
)

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

@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
)

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!

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.

Thank you, @AlexisOlson , works perfectly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors