Why is my average on Raw Subtotal giving me the wrong number?

[Zack92]

Hey there,

I'm trying to calculate the average on the raw subtotal using the ISINSCOPE function, but I'm getting the wrong answer. If you take a look at the "red" column, the average should be 35,401.9675 (141.608 divided by 4), but I'm getting 6.743.26, which is completely off. Can someone please help me fix this?

$ Won_Avg_Test = IF(ISINSCOPE(df_Opportunity_wAcct_wQL_wLost[QL.ProductCode (groups)]),CALCULATE(SUM(df_Opportunity_wAcct_wQL_wLost[QL.CustomerTotal.Converted]), FILTER(df_Opportunity_wAcct_wQL_wLost, 
                    df_Opportunity_wAcct_wQL_wLost[StageName] = "9 - Closed Won" || 
                    df_Opportunity_wAcct_wQL_wLost[StageName] = "Closed Won")), CALCULATE(AVERAGE(df_Opportunity_wAcct_wQL_wLost[QL.CustomerTotal.Converted]), FILTER(df_Opportunity_wAcct_wQL_wLost, 
                    df_Opportunity_wAcct_wQL_wLost[StageName] = "9 - Closed Won" || 
                    df_Opportunity_wAcct_wQL_wLost[StageName] = "Closed Won")))

Power BI File 

https://drive.google.com/file/d/1di_W7jEBMYia042bsEN9Dy5m5OBAN1GW/view?usp=sharing
Data
https://drive.google.com/file/d/1o1KYZ2oU_4EiMkSCWEMLAJETedBca3gQ/view?usp=sharing

Thank you so much

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below.

$ Won_Avg_Test =
IF (
    ISINSCOPE ( df_Opportunity_wAcct_wQL_wLost[QL.ProductCode (groups)] ),
    CALCULATE (
        SUM ( df_Opportunity_wAcct_wQL_wLost[QL.CustomerTotal.Converted] ),
        FILTER (
            df_Opportunity_wAcct_wQL_wLost,
            df_Opportunity_wAcct_wQL_wLost[StageName] = "9 - Closed Won"
                || df_Opportunity_wAcct_wQL_wLost[StageName] = "Closed Won"
        )
    ),
    AVERAGEX (
        VALUES ( df_Opportunity_wAcct_wQL_wLost[QL.ProductCode (groups)] ),
        CALCULATE (
            SUM ( df_Opportunity_wAcct_wQL_wLost[QL.CustomerTotal.Converted] ),
            df_Opportunity_wAcct_wQL_wLost[StageName] = "9 - Closed Won"
                || df_Opportunity_wAcct_wQL_wLost[StageName] = "Closed Won"
        )
    )
)

[Zack92]

@Jihwan_Kim Thank you so much!