Values greater than average duration

[Jos13]

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 

[AntrikshSharma]

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]

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]

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

[AntrikshSharma]

Try this: Measure = VAR AvgRes = [Avg_resolution] VAR IssuesHigherThanAvg = FILTER ( Table, [Average Resolution] > AvgRes ) VAR Result = CALCULATE ( [Avg_resolution], IssuesHigherThanAvg ) RETURN Result

[Jos13]

Hi,

This measure returns a different result.

Please find the pbix file below

https://drive.google.com/file/d/1UdmNQzsyCsLJ1qKT-4_7fkYzvDOHoqcn/view?usp=sharing

[AntrikshSharma · Accepted solution]

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

[Jos13]

Thank you @AntrikshSharma 

Much appreciated 🙂.

[AntrikshSharma]

Great! You're welcome! Have a great day! 🙂

[amitchandak]

@Jos13 , Try like

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

[Jos13]

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]

@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])