DAX for matrix table

[Kay_Kalu]

From the image above I will like it on a matrix table.  Having issues with the highlighted column the fomula i have used in excel is below 

=IFERROR(ROUND(
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", C4:C11, ">0") +
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", D4:D11, ">0") -
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", C4:C11, ">0", D4:D11, ">0"),
2), 0)
will like to repliate this in dax.

[danextian]

Hi @Kay_Kalu 

Please try the following measure:

VAR _avg1 =
    AVERAGEX (
        FILTER (
            SUMMARIZECOLUMNS (
                'table'[Checking Parent],
                "@value1", [total number of checks  measure],
                "@value2", [total sql no feedback measure],
                "@value3", [sql fail rate]
            ),
            [@value1] > 0
                && [@value2] > 0
        ),
        [@value3]
    )
VAR _avg2 =
    AVERAGEX (
        FILTER (
            SUMMARIZECOLUMNS (
                'table'[Checking Parent],
                "@value1", [total number of checks  measure],
                "@value2", [total sql feedback measure],
                "@value3", [total sql no feedback measure]
            ),
            [@value1] > 0
                && [@value2] > 0
        ),
        [@value3]
    )
VAR _avg3 =
    AVERAGEX (
        FILTER (
            SUMMARIZECOLUMNS (
                'table'[Checking Parent],
                "@value1", [total number of checks  measure],
                "@value2", [total sql feedback measure],
                "@value3", [total sql feedback measure],
                "@value4", [sql fail rate measure]
            ),
            [@value1] > 0
                && [@value2] > 0
                && [@value3] > 0
        ),
        [@value4]
    )
RETURN
    ROUND ( _avg1 + _avg2 - _avg3, 2 )

If this doesn't work, please provide a sanitized copy of your PBIX (confidential data removed) stored in the cloud.

[Kay_Kalu]

Thanks for your reply this also has work. Really appreicate the effort. 

[Shahid12523]

Combined SQL Fail Rate =
VAR AvgNoFeedback =
AVERAGEX(
FILTER('YourTable',
'YourTable'[Total Number of Checks] > 0 &&
'YourTable'[Total SQL No Feedback] > 0
),
'YourTable'[SQL Fail Rate]
)

VAR AvgFeedback =
AVERAGEX(
FILTER('YourTable',
'YourTable'[Total Number of Checks] > 0 &&
'YourTable'[Total SQL Feedback] > 0
),
'YourTable'[SQL Fail Rate]
)

VAR AvgBoth =
AVERAGEX(
FILTER('YourTable',
'YourTable'[Total Number of Checks] > 0 &&
'YourTable'[Total SQL No Feedback] > 0 &&
'YourTable'[Total SQL Feedback] > 0
),
'YourTable'[SQL Fail Rate]
)

RETURN
ROUND(AvgNoFeedback + AvgFeedback - AvgBoth, 2)

[Kay_Kalu]

Thanks for your reply but this hasn't worked maybe because the columns are dax derived. see below
Total Number of Checks  = CALCULATE(DISTINCTCOUNT(JIRA_Refined_Data[jira_id]))

Total SQL Feedback =
VAR FilteredTable =
FILTER (
JIRA_Refined_Data,
JIRA_Refined_Data[feedback category] = "SQL"
)
VAR DistinctPerJira =
SUMMARIZE (
FilteredTable,
JIRA_Refined_Data[jira_id],
"DistinctItems", DISTINCTCOUNT ( JIRA_Refined_Data[item_ref] )
)

VAR Result =
SUMX( DistinctPerJira, [DistinctItems] )
RETURN
IF ( ISBLANK(Result), 0, Result )

Total SQL No Feedback =
IF (
ISBLANK(SELECTEDVALUE('JIRA_Refined_Data'[checking_parent])),
SUMX (
VALUES('JIRA_Refined_Data'[checking_parent]),
VAR Initial_Parent = 'JIRA_Refined_Data'[checking_parent]
VAR JiraCount = CALCULATE(
DISTINCTCOUNT('JIRA_Refined_Data'[jira_id]),
'JIRA_Refined_Data'[checking_parent] = Initial_Parent
)
VAR SQLWeight = LOOKUPVALUE(
'Task Weighting'[SQL Weighting],
'Task Weighting'[Checking Parent],
Initial_Parent
)
RETURN (JiraCount * SQLWeight) - [Total SQL Feedback]
),
VAR Initial_Parent = SELECTEDVALUE('JIRA_Refined_Data'[checking_parent])
VAR JiraCount = CALCULATE(
DISTINCTCOUNT('JIRA_Refined_Data'[jira_id]),
'JIRA_Refined_Data'[checking_parent] = Initial_Parent
)
VAR SQLWeight = LOOKUPVALUE(
'Task Weighting'[SQL Weighting],
'Task Weighting'[Checking Parent],
Initial_Parent
)
VAR Result =
(JiraCount * SQLWeight) - [Total SQL Feedback]
RETURN
IF ( ISBLANK(Result), 0, Result )
)

SQL Fail Rate =
VAR fail_rate = 100 - (DIVIDE(
[Total SQL No Feedback],
([Total SQL No Feedback] + [Total SQL Feedback]),
0) *100
)
return IF([Total SQL No Feedback] = 0, 0, fail_rate)

so is working out the below excel formula in dax for the highlighed row
=IFERROR(ROUND(
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", C4:C11, ">0") +
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", D4:D11, ">0") -
    AVERAGEIFS(E4:E11, $B$4:$B$11, ">0", C4:C11, ">0", D4:D11, ">0"),
2), 0)

[Shahid12523 · Accepted solution]

Combined SQL Fail Rate =
VAR BaseTable =
ADDCOLUMNS(
VALUES('JIRA_Refined_Data'[checking_parent]),
"TotalChecks", [Total Number of Checks],
"SQLNoFeedback", [Total SQL No Feedback],
"SQLFeedback", [Total SQL Feedback],
"FailRate", [SQL Fail Rate]
)

VAR AvgNoFeedback =
AVERAGEX(
FILTER(
BaseTable,
[TotalChecks] > 0 && [SQLNoFeedback] > 0
),
[FailRate]
)

VAR AvgFeedback =
AVERAGEX(
FILTER(
BaseTable,
[TotalChecks] > 0 && [SQLFeedback] > 0
),
[FailRate]
)

VAR AvgBoth =
AVERAGEX(
FILTER(
BaseTable,
[TotalChecks] > 0 && [SQLNoFeedback] > 0 && [SQLFeedback] > 0
),
[FailRate]
)

RETURN
ROUND(AvgNoFeedback + AvgFeedback - AvgBoth, 2)

[Kay_Kalu]

Thanks this has worked