Average on condition - Matrix

[Ezhilmathi]

Hi All,

I want to calculate the percentage average of a column which has 0 1 and 0 in which 0 means atleast 1 job had failed and 1 means no failures. This must be shown in a matrix visual and by all the level of hierarchies of date i.e Year, Quarter, Month, Week, Date. The count of 1s in the column must be calculated and then the average of the column in week, month, quarter, year, project, etc.
I am struck on how to find the % average of the 1s in the column by week i.e. Count(1)/Count(1+0) in the column by all the hierarchy.

avg_test =
CALCULATE(AVERAGE(Prod_Jobs_Snow[IsJobFailed]),
ALLEXCEPT(Prod_Jobs_Snow, Prod_Jobs_Snow[Product], Prod_Jobs_Snow[Project], Dim_Date[Year], Dim_Date[Quarter], Dim_Date[Month], Dim_Calendar[Week of Month],Dim_Date[Date]))

This is my current formula and don't get the correct answer wher inserted if condition to them to filter out 1s in the column.

For example, from the above image, the average value of week 5 should be Count(100s)/Count(100s+0s) = 5/6. But I am getting the overall percentage instead. 

Would appreciate any help on this.

[v-yinliw-msft · Accepted solution]

Hi @Ezhilmathi ,

You can try this method:

avg_test = 
VAR _Count1 =
    CALCULATE (
        COUNT ( Prod_Jobs_Snow[IsJobFailed] ),
        FILTER (
            ALL ( Prod_Jobs_Snow ),
            [IsJobFailed] = 0
                && [Product] = MAX ( Prod_Jobs_Snow[Product] )
                && [Project] = MAX ( Prod_Jobs_Snow[Project] )
        ),
        ALLEXCEPT ( Dim_Date, Dim_Date[Year], Dim_Date[Quarter], Dim_Date[Month] ),
        ALLEXCEPT ( Dim_Calendar, Dim_Calendar[Week of Month] )
    )
VAR _Count2 =
    CALCULATE (
        COUNT ( Prod_Jobs_Snow[IsJobFailed] ),
        ALLEXCEPT ( Prod_Jobs_Snow, Prod_Jobs_Snow[Product], Prod_Jobs_Snow[Project] ),
        ALLEXCEPT ( Dim_Date, Dim_Date[Year], Dim_Date[Quarter], Dim_Date[Month] ),
        ALLEXCEPT ( Dim_Calendar, Dim_Calendar[Week of Month] )
    )
RETURN
    DIVIDE ( _Count1, _Count2 )

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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