Forum Discussion

Jos13's avatar
Jos13
Helper III
6 years ago
Solved

Values greater than average duration

Hi All,

I have the following data

I have a measure as follows

Avg_resolution = AVERAGEX('Table','Table'[R_Time_sec])

Is it possible to display those issues with average resolution greater than the average value(17,330). The required table visual will have "Issue 2". Kindly help me on this.

 

Thanks in advance 

Need Help
Tips and Tricks
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    Thanks for the file, use this:

    Measure 4 =
VAR AvgRes =
    CALCULATE ( [Avg_resolution], ALL ( 'Table'[Issue] ) )
VAR Result =
    CALCULATE (
        [Avg_resolution],
        FILTER ( VALUES ( 'Table'[Issue] ), [Avg_resolution] > AvgRes )
    )
RETURN
    Result

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Jos13 , Try like

    measure =
    var _avg = calculate([Avg_resolution],all(Table))
    return
    Countx(filter(summarize('Table'[Job],"_1", [Avg_resolution]),[_1]>__avg),[Job])

    • Jos13's avatar
      Jos13
      Helper III
      6 years ago

      Hi amitchandak ,

      I can filter the "Issue" using the measure suggested by you. This works only when I add the measure to table visual.

      I don't want to display measure in this visual. I just wanted to show issue with its duration.

      Please let me know if it would be possible.

       

      Thanks,

      Jos

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Jos13 , My bad, it should be issues

         

        measure =
        var _avg = calculate([Avg_resolution],all(Table))
        return
        Countx(filter(summarize('Table','Table'[issues],"_1", [Avg_resolution]),[_1]>__avg),[Job])

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    if you want to show a list of issues greater than average then you could try this:

    =
VAR AvgRes = [Avg_resolution]
VAR IssuesHigherThanAvg =
    FILTER (
        ADDCOLUMNS ( Table, "Average Resolution", [Avg_resolution] ),
        [Average Resolution] > AvgRes
    )
VAR Result =
    CONCATENATEX (
        IssuesHigherThanAvg,
        Table[Issue] & " " & [Average Resolution],
        ","
    )
RETURN
    Result
    • Jos13's avatar
      Jos13
      Helper III
      6 years ago

      Hi AntrikshSharma ,

      This measure works fine. I tried to exclude the concatenate part since it is not required, then it generated some error.

      My expected result will look like this

      Thanks,

      Jos

      • Jos13's avatar
        Jos13
        Helper III
        6 years ago

        Sorry for the confusion.

         

        The requirement is as follows. I only wanted to show issues with resolution time greater than the average resolution time.

         

        Thanks,

        Jos