Jump to content

Top 10 [Category] by [Count of another Category]

Woody Null

Recommended Posts

My data set is about 4M rows. Each row has a categorical[Arrival Status] which could be On Time, Late, or Early. Each row also has a [Lane] categorical of which there are thousands of possible values.

I have a stacked bar with [Lane] on the category axis, (Row Count) on the value axis, and [Arrival Status] on the color by. So far so good. BUT,I need to only show the top 10 lanes, by number of on time rows. In other words which lanes have more on times than any other. AND, at the same time, still show the number/percent of lates,earlies, and on times.

At first I thought I could use Show/Hide Items,but if I do top 10 value axis, it groups by color and hides the lates and earlies, giving me an incomplete picture of each lane.

So then I created a calculated column to find the total number of On Times per lane: SUM(CASE WHEN [Arrival Status]="ONTIME" THEN 1 ELSE 0 END) OVER [Lane]

But I still run into the issue of being unable to only show the top 10 on time lanes in the visual. The Show/Hide clips the data in weird places.

Anyone have any suggestions

Link to comment
Share on other sites

  • 2 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...