help with my Coalesce

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 

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 

Anonymous 

apologies I'm outside the office now and replying through my phone. 

No just summarize the fact table using the columns from both fat table and date table. Also please clarify if at the total levels you want to see the average of the averages or the sum of the averages?

Hi Anonymous 
Sorry, I just returned back to office.

Please try the following for average of the averages and sum of the averages.

verage COALESCE =
AVERAGEX (
    SUMMARIZE (
        'FACT TABLE',
        'Calendar'[Year],
        'Calendar'[Quarter],
        '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 )
            )
        )
    )
)

Sum COALESCE =
SUMX (
    SUMMARIZE (
        'FACT TABLE',
        'Calendar'[Year],
        'Calendar'[Quarter],
        '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 )
            )
        )
    )
)

Anonymous 
Try this

Sum COALESCE =
SUMX (
    SUMMARIZE (
        'FACT TABLE',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'FACT TABLE'[Charger Type],
        'FACT TABLE'[Connector]
    ),
    CALCULATE (
        IF (
            NOT ISEMPTY ( CLACULATETABLE ( 'FACT TABLE' ) ) 
                && NOT ISEMPTY CLACULATETABLE ( ( 'Calendar' ) ),
            AVERAGEX (
                VALUES ( 'Calendar'[Date] ),
                COALESCE ( [SDR ID average per CP ID], 0 )
            )
        )
    )
)

Anonymous 

Please fix the spilling of CALCULATETABLE 

Anonymous 

Sorry my mistake 

Sum COALESCE =
SUMX (
    SUMMARIZE (
        'FACT TABLE',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'FACT TABLE'[Charger Type],
        'FACT TABLE'[Connector]
    ),
    CALCULATE (
        IF (
            NOT ISEMPTY ( CALCULATETABLE ( 'FACT TABLE' ) )
                && NOT ISEMPTY ( CALCULATETABLE ( 'Calendar' ) ),
            AVERAGEX (
                VALUES ( 'Calendar'[Date] ),
                COALESCE ( [SDR ID average per CP ID], 0 )
            )
        )
    )
)

Anonymous 

This is tricky, I faced similar problem before but this is different. Please try

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

Its "Start Date" 

Anonymous 

I thought this what you want. This calculating the sum of the averages. Can you advise what results are you expecting to have at each level?

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

Thank you tamerj1, 
Please assist arranging this. Thank you kindly. 

Hi tamerj1, 

It's almost perfect. 
Forget the verage COALESCE; Must focus on Sum COALESCE = 

If we take Q3 for example, we only have July. 
The formula now gives us correct everything, including correct total for Jul, but I need Q3 to display the July total not Q3 total. 
The same with Q1. 
I hope this makes sense. 
Please see if you can figure this out.
Thank you thank you thank you.
J
 

Hi tamerj1, 

Nope. 

Thank you, but there's some other error. 

Hi tamerj1, 

Thank you; That looks really good, but no.
The results are exactly the same as the one we've tried earlier. 

Tricky indeed, we've got a small error.
 

Hi tamerj1, 

This seems impossible to resolve. Sum COALESCE 0.4 column has your newest formula. Thank you for trying, much appreciate your assistance. 
😞  

Thank you so much, 
The two SUM formulas are the same, giving me the exact smae results. Both are perfect with an exception. 
For example in Q1 - I only have data starting in March. That is when the unit became operational. The 0.17 is not good for Q1. I need Q1 to show 0.48 as it is for March instead. 
The same for Q3, My Q3 data is incomplete so it averages the usage based on July/August & September, but we don't have yet the data for August nor September, so the true existing average for Q3 should be 2.19, but it is showing 0.74. 
This makes my line graph look very bad. 
I hope this makes sense now. Please let me know if it doesn't.