Forum Discussion

saud968's avatar
saud968
Memorable Member
2 years ago
Solved

Incorrect average

AGE 70 27 25 9 4 4 2 9 7 2 0 3 2 0 0 13 0 3 0 6 0 1 6 4 15 4 6 13 5 3 11 3 4 2 0 0 1 2 2 1 2 5 0 1 4 5 4 4 3 3 3 4 1 2 1 2 1 7 1 0 1 8 0 5 0 7 6 4 1 3 1
In Excel the average for this is 5.04, however, same age in Powerbi using the below DAX the average we are getting is 10 how do we fix the Dax, both tables do not have the same columns so the union does not work. I tried. 
TTR2 = CALCULATE( AVERAGEX( 'support__cw_ops_manage_case_merge_temp', 'support__cw_ops_manage_case_merge_temp'[AGE] ) + AVERAGEX( 'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS', 'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS'[AGE_IN_DAY] ) )

Please help @amitchandak @Idrissshatila @Ritaf1983 @Fowmy @Ahmedx lbendlin Daniel29195 

General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • saud968's avatar
    saud968
    2 years ago

    Thank you for reviewing it. I was able to get the correct average with the below dax

    TTR2 =
    VAR AvgAge1 =
        AVERAGEX (
            FILTER (
                'support__cw_ops_manage_case_merge_temp',
                'support__cw_ops_manage_case_merge_temp'[ClosedCaseFlag] = TRUE ()
            ),
            'support__cw_ops_manage_case_merge_temp'[AGE]
        )
    VAR AvgAge2 =
        AVERAGEX (
            FILTER (
                'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS',
                'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS'[ClosedCaseFlag] = TRUE ()
            ),
            'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS'[AGE_IN_DAY]
        )
    RETURN
        AvgAge1 + AvgAge2

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    How does the DAX shown relate to the data you have posted?

    --

    The DAX refers to 2 tables and, it looks to me, that the CALCULATE is redundant and then 2 averages are getting added together. 

    So, if the average of each table's value is around 5, adding them together will give 10.  That may explain why you get 10 but I'm still confused about the data and the dax shown.

    • saud968's avatar
      saud968
      Memorable Member
      2 years ago

      Thank you for reviewing it. I was able to get the correct average with the below dax

      TTR2 =
      VAR AvgAge1 =
          AVERAGEX (
              FILTER (
                  'support__cw_ops_manage_case_merge_temp',
                  'support__cw_ops_manage_case_merge_temp'[ClosedCaseFlag] = TRUE ()
              ),
              'support__cw_ops_manage_case_merge_temp'[AGE]
          )
      VAR AvgAge2 =
          AVERAGEX (
              FILTER (
                  'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS',
                  'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS'[ClosedCaseFlag] = TRUE ()
              ),
              'SUPPORT__CW_OPS_VOLUME_FRESHDESK_FORTIFY_SPS'[AGE_IN_DAY]
          )
      RETURN
          AvgAge1 + AvgAge2