Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

help with my Coalesce

Hi, 
I hope someone can help. I am stuck.
Below are two different DAX functions to calculate my averages.
The first one (Average Coalesce function) gives me the precise results with full data e.g. Q2 2022, but it doesn't calculate Q3 correctly as I do not have the data for August/September.

The second (Average Connector Usage) is not as precise in the result but it calculates the Quarter averages correctly.

 

These are the results. 

 

 

 

It all started with my base function to create further functions:

 

SDR ID average per CP ID =

AVERAGEX(
    KEEPFILTERS(VALUES('FACT TABLE'[CP ID])),
    CALCULATE(COUNTA('FACT TABLE'[SDR ID]))
)
 

SDR ID is my unique transaction number and CP ID is my unique location number.

I have tried many measures and these two are the closest to return correct results.

 

No.1. 

Average COALESCE =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)
 

No.2. 

Average Connector Usage =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    VAR minDate =
        MIN ( 'FACT TABLE'[Start Date] )
    VAR maxDate =
        MAX ( 'FACT TABLE'[Start Date] )
    RETURN
        CALCULATE (
            AVERAGEX (
                VALUES ( 'Calendar'[Date] ),
                COALESCE ( [SDR ID average per CP ID], 0 )
            ),
            DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
        )
)
 
 
Would anyone have and idea hot to fix it please? 
Thank you kindly, 
J
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Please use 

    Sum COALESCE 0.5 =
SUMX (
    SUMMARIZE (
        'FACT TABLE',
        'FACT TABLE'[Connector],
        'Calendar'[Year],
        'Calendar'[Quarter]
    ),
    CALCULATE (
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE ( 'FACT TABLE', 'Calendar'[Month], 'FACT TABLE'[Charger Type] ),
                CROSSFILTER ( 'FACT TABLE'[Start Date], 'Calendar'[Date], BOTH )
            ),
            CALCULATE (
                AVERAGEX (
                    VALUES ( 'Calendar'[Date] ),
                    COALESCE ( [SDR ID average per CP ID], 0 )
                )
            )
        )
    )
)
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Following is the solution

    Sum COALESCE 0.5 =
AVERAGEX (
    SUMMARIZE ( 'FACT TABLE', 'Calendar'[Year], 'Calendar'[Quarter] ),
    CALCULATE (
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE ( 'FACT TABLE', 'Calendar'[Month], 'FACT TABLE'[Charger Type] ),
                CROSSFILTER ( 'FACT TABLE'[Start Date], 'Calendar'[Date], BOTH )
            ),
            CALCULATE (
                AVERAGEX (
                    VALUES ( 'Calendar'[Date] ),
                    COALESCE ( [SDR ID average per CP ID], 0 )
                )
            )
        )
    )
)

37 Replies

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

    Hi Anonymous 
    Please try 

    Average COALESCE =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        SUMMARIZE (
            'FACT TABLE',
            'FACT TABLE'[Column1],
            'Calendar'[Year],
            'Calendar'[Quarter]
        ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

    Where 'FACT TABLE'[Column1] is the column you are placing in the rows of the pivot table.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you tamerj1, 
      Much appreciated but the Average COALESE2 is giving me some weird totals. 

       

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

        Hi Anonymous 
        This is highly dependant on the existing filter context. When you change the filter context you have to expect different results. The filter context in the pivot table screenshot is different than the filter context in the matrix screenshot. Also I have noticed that there are actually two columns placed in the rows of the matrix. Both columns must be included in the SUMMARIZE table. Would you please provide the names of these columns in the form TableName[ColumName] in order to support you further. Thank you for your Patience.

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    It's pretty much obvious why the two return different results. The first one works with all dates from Calendar visible in the current context, even though there are no values for the dates in the fact table. On top of that, using COALESCE makes sure that such dates are assigned 0 as the value instead of BLANK, which makes the value of the average go down.

     

    On the other hand, the second measure looks at the data in the fact table and retrieves the min and max dates from there. Then adjusts the context to only use the dates from Calendar that are between those 2 dates, so some dates from the Calendar might be rejected (and surely are since you're getting different results!). Still, you are using COALESCE which means you're treating BLANKS as 0's. Not sure if this is what you want because then your averages go down...

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi daXtreme, 
      Thank you. 
      I need the SDR ID average per CP ID becasue I need the average unique SDR ID per CP ID. 
      Then I need average use of the unique usage per CP ID - my columns: Charger Type & Connector
      I need COALESCE to give me correct averages per day including 0's in which the Charger Type & Connector weren't used that day, but I don't want to account for the Months in the Quarter, like Q3 August/September. 
      I hope this makes sense
      See, if I use my Average COALESCE it just gives me the accurate calculations, but it doesn't account for the additional months in Q1 or Q3

       

       

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

    Hi Anonymous 
    Please use 

    Sum COALESCE 0.5 =
SUMX (
    SUMMARIZE (
        'FACT TABLE',
        'FACT TABLE'[Connector],
        'Calendar'[Year],
        'Calendar'[Quarter]
    ),
    CALCULATE (
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE ( 'FACT TABLE', 'Calendar'[Month], 'FACT TABLE'[Charger Type] ),
                CROSSFILTER ( 'FACT TABLE'[Start Date], 'Calendar'[Date], BOTH )
            ),
            CALCULATE (
                AVERAGEX (
                    VALUES ( 'Calendar'[Date] ),
                    COALESCE ( [SDR ID average per CP ID], 0 )
                )
            )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tamerj1, 

      Can I please request one more adjustment. 
      All sub-functions work perfectly, except I need the Total to be the Average of the Quarters and show 1:68. Not Sum of the Quarters 5.04 as below. 
      Thank you so much. 

       

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

        Hi Anonymous 

        just replace the outer SUMX with AVERAGEX 

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi there.

     

    What's the explanation of the following line in [SDR ID average per CP ID]?

     

    KEEPFILTERS(VALUES('FACT TABLE'[CP ID]))

     

    Why do you think you need KEEPFILTERS here?