Forum Discussion

Kay_Kalu's avatar
Kay_Kalu
Helper I
10 months ago
Solved

DAX for matrix table

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.

 

Need Help
Tips and Tricks
  • Shahid12523's avatar
    Shahid12523
    10 months ago

    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)

6 Replies

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    10 months ago

    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's avatar
      Kay_Kalu
      Helper I
      10 months ago

       

      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's avatar
        Shahid12523
        Community Champion
        10 months ago

        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)

  • danextian's avatar
    danextian
    Super User
    10 months ago

    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's avatar
      Kay_Kalu
      Helper I
      10 months ago

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