help with my Coalesce

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[Anonymous]

That is the one! It works like magic. Thank you tamerj1
Much appreciated.

Best, J

[tamerj1]

Hi @Anonymous 

just replace the outer SUMX with AVERAGEX 

[Anonymous]

Thank you tamerj1, 

That's what I thaught. I've tried that but here are the results. 
I just called it Sum COELESCE 0.6 for now. 
It changes everything. 
I just need the 2022 total to be the average, everything esle should stay the same. 

[daXtreme]

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]

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

[daXtreme]

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?

[tamerj1]

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]

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

[tamerj1]

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.

[Anonymous]

Thank you tamerj1 for your patience, 
My columns are

'FACT TABLE'[Charger Type]

then 

'FACT TABLE'[Connector]

[tamerj1]

Hi @Anonymous 
PLease try 

verage COALESCE =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        SUMMARIZE (
            'FACT TABLE',
            'FACT TABLE'[Charger Type],
            'FACT TABLE'[Connector]
        ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

[Anonymous]

Hi tamerj1, 
Thank you kindly, but Nope. Your new function Average COALESCE3 returns some weird totals. 

Average COALESCE3 =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
AVERAGEX (
SUMMARIZE (
'FACT TABLE',
'FACT TABLE'[Charger Type],
'FACT TABLE'[Connector]
),
COALESCE ( [SDR ID average per CP ID], 0 )
)
)

[tamerj1]

@Anonymous 
I think you are looking for a daily average.

Please try

verage COALESCE =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        SUMMARIZE (
            'FACT TABLE',
            'FACT TABLE'[Charger Type],
            'FACT TABLE'[Charger Type],
            'Calendar'[Date]
        ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

[Anonymous]

🙂 Thank you tamerj1
Your 'verage COALESCE' is not working I'm afarid.
Thank ou so much for trying. 

verage COALESCE =

IF (

    NOT ISEMPTY ( 'FACT TABLE' ),

    AVERAGEX (

        SUMMARIZE (

            'FACT TABLE',

            'FACT TABLE'[Charger Type],

            'FACT TABLE'[Connector],

            'Calendar'[Date]

        ),

        COALESCE ( [SDR ID average per CP ID], 0 )

    )

)

[tamerj1]

@Anonymous 

Thank you for your patience. Please try

verage COALESCE =
AVERAGEX (
    SUMMARIZE (
        'FACT TABLE',
        'FACT TABLE'[Charger Type],
        'FACT TABLE'[Charger Type]
    ),
    CALCULATE (
        IF (
            NOT ISEMPTY ( 'FACT TABLE' ),
            AVERAGEX (
                VALUES ( 'Calendar'[Date] ),
                COALESCE ( [SDR ID average per CP ID], 0 )
            )
        )
    )
)

[Anonymous]

Hi tamerj1, 

Thank you;
Although the 'erage COALESCE' calculates the averages correctly, the totals are incorrect. 

 I have amended your function (where in red, as I was receiving an error when used

'FACT TABLE'[Charger Type] twice. 

erage COALESCE =

AVERAGEX (

    SUMMARIZE (

        'FACT TABLE',

        'FACT TABLE'[Charger Type],

        'FACT TABLE'[Connector]

    ),

    CALCULATE (

        IF (

            NOT ISEMPTY ( 'FACT TABLE' ),

            AVERAGEX (

                VALUES ( 'Calendar'[Date] ),

                COALESCE ( [SDR ID average per CP ID], 0 )

            )

        )

    )

)

[tamerj1]

@Anonymous 

Yes this supposed to work in the previous tmatrix where you don't have the year and the quarter. Now you need to add year and quarter columns to the SUMMARIZE