Incoherent Totals

[Anonymous]

Hi, 

I hope someone will have an idea what is happening here. 

I have this beatutiful formula that works, mostly, except I am not sure how it sums up the totals. 

The total should be 1.44. It doesn't make any sense why and how it gets 1.22

In my table:
Rows: Connector & Calendar Table
Columns: Charger Type
Values: Average Connector Usage formula (below)

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

 Many thanks, 
J

[v-chenwuz-msft]

Hi @Anonymous ,

The total row is not just row1 result + row2 result+..., it is the all the table's context for the measure to calcualte. And the 1.22 is average of all the fact table.

If you want sum (0.19, 0.46,0.79) , you can create use HASONVALUE(), like the following:

Average Connector Usage =
IF (
    HASONEVALUE ( 'fact table'[connector] ),
    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 )
            )
    ),
    SUMX (
        SUMMARIZE (
            'fact table',
            [connector],
            "a",
                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 )
                    )
        ),
        [a]
    )
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-chenwuz-msft,
Thank you so much for your formula but it is not giving me the correct results.

I've previously included formula for the 'Average Connector Usage'. 
Below is the formula for 'Average COALESCE'

Average COALESCE =

IF (

    NOT ISEMPTY ( 'FACT TABLE' ),

    AVERAGEX (

        VALUES ( 'Calendar'[Date] ),

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

    )

)

The formula you have sent I re-named 'Average Connector Usage3'
From all the formulas the 'Average COALESCE' calculates the usage most precicely, with the exception that it doesn't calculate the Quarters correctly.
Q2: we have the complete data Apr-May-Jun. Comparing with Excel calculations the Average Coalesce gives me the accurate data, but in the Q3 we only have the July data, it is not picking up the zeros for August and September. 

Please see if you can come up with an alternative solution. 
Thank you kindly, 
J

PREVIEW

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907