How to make a chart or table with cutoff bucket

[bobbrow]

I have a table with data that reports on how long it takes for my users to accomplish a task.  The majority of users finish it in a few seconds, but I have several outliers who are outside of that range.  I'd like the graph or table to aggregate all of those outliers into a single bucket.

For example I'd like to transform a table like this:

Seconds | UserCount

---------

1 | 1000

2 | 500

3 | 200

4 | 25

5 | 10

10 | 1

20 | 1

50 | 1

Into something like this:

Seconds | UserCount

---------

1 | 1000

2 | 500

3 | 200

4 | 25

5 | 10

6+| 3

Is this possible?  Or does it need to be done in the data source before it hits PowerBI?

[bobbrow]

For additional context, I know that I can truncate the axis for my charts to cut off the outliers and make the chart useful to read, but I don't want to lose sight of them in case it turns out that there is a bigger problem I need to investigate.

[V-lianl-msft · Accepted solution]

Hi @bobbrow ,

Try to create a calculated column using DAX.

COL = IF(table[Seconds]>=6,"6+",table[Seconds])

Replace Seconds column with this column and put it into the chart.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bobbrow]

Thank you.  Just a note that I couldn't mix a string with a number in the calculated column, so I had to use FORMAT on table[Seconds].  If there's another way to do this, I'd be interested to know since I don't know how to hide intermediate labels on the axis when the numbers are converted to strings besides rounding all the intermediate values to whole numbers.  (My real world case has floating point numbers, not whole numbers like the example I posted)