Top 10 results for Chart View

[MauriceWhelan]

Hi. Would it be possible to add an option to the Chart view that can allow the results returned to be restricted to the top 10 of whatever dataset is involved. For example, I have an existing scenario where I would be looking to present the top 10 [Assets] with the highest number of Maintenance Tickets or Cost or Labor Hours etc… The dataset I have is too big to graph without a filter like that.

[Steve]

Wouldn’t a slice offer that capability? With a row filter condition like following:

IN(
  [_THISROW],
  TOP(
    ORDERBY(
      list-of-row-keys,
      sort-key-1,
      reverse?-1,
      ...,
      sort-key-n,
      reverse?-n
    ),
    10
  )
)

[MauriceWhelan]

Thank you for that @Steve. I was thinking a slice might help but wasn’t sure of the correct expression. Still a little unsure how to insert my setup into your sample expression.

I have [Record ID] as key column and I want to sort by [Asset].

I have IN([_THISROW],TOP(ORDERBY([Record ID],[Asset]),10)) but not working.

Could you advise a little more?

Thanks.

[Aleksi]

Would you please take a printscreen from that situation and how do you want to change it. What kind of chart are you trying to create? Probably a histogram?

[MauriceWhelan]

Yes @Aleksi you are correct it is a Histogram. I have attached a screenshot of a sample chart based on Asset by Cost. Ideally I would like to be able to present a top 10 through the editor. The slice suggestion above would work but can’t quite get the expression correct.

[Aleksi]

You would like to show only 10 highest bars?

[MauriceWhelan]

Exactly

[Aleksi]

For that purpose you need to create a virtual column where you group all same assets with costs. That would be SUM(SELECT(Data[Cost],[Asset]=[_THISROW].[Asset])). Then you need to create a slice for your chart. That slice condition would be something like… IN([VirtualCosts],TOP(SORT(SELECT(Data[VirtualCosts],TRUE,TRUE),TRUE),10)). I didn’t test but it should work.

[MauriceWhelan]

Great, thank you @Aleksi. I will test.

[Aleksi]

Though it would be much easier if you could just choose it from the chart’s definition.

[MauriceWhelan]

Yes definitely. It would be great to be able to select different filters when configuring the chart.