Filter for Top 10 Values in Hor Grouped Bar (Similar to Top 10 Excel Pivot Table Function)

kcoughlan65

Hello everyone!

I've read a lot of posts and YouTube videos in regard to Rank and Window functions within DOMO Beast Modes and I feel like I'm so close to nailing down what I am looking for and was wondering if anyone would be able to help me out.

I am basically trying to duplicate the Top 10 function in an excel pivot table by showing the Top 10 CPT codes (we are a medical company) by their value and count.

I'm currently using a grouped bar with the Year being the determining factor between CPT codes (one line for each year). So there are thousands of CPT codes, right now I just zoomed in to the top section but at the end of the day that's how I would want the card to show, the top 10 in terms of Sum of Value.

Ideally I'd want to say, aggregate the sum of value for each CPT Code (regardless of year) but then be able to show the breakout by each year in the card (so in theory should be 20 lines because the top 10 overall CPT codes with 1 line for 2021 and 1 line for 2022).

I also want to be able to create this in Beast Mode within the Card or can do a formula within the New Magic ETL, but I don't want to drastically change the dataset.

Here is an example of the dataset:

Obviously a lot more rows and CPT codes in the actual dataset, the good news is that I don't want to aggregate by provider, just the CPT code level.

Here are two formulas I tried but was running into some errors:

rank() over (PARTITION BY `CPTCode` order by sum(`Value`) desc)

This only gave me a rank of 1 or 2 so I think it's doing the summation of all CPT Codes by year (i.e. the total value of ALL CPT codes in 2021 and ALL CPT codes in 2022) and then if I filter for 1, it only shows me the year 2022

rank() over ( order by sum(`Value`) desc)

This is closer to what I think I should have because this gave me a rank number of like 500+ that I can filter off of. The problem is if I filter for the top 20 ranks, there are some CPT codes where the value for 2022 is included but not 2021 since it seems to be taking the datapoint of Year + CPT Code for the rank(i.e. the CPT Code 50155 in 2021 was the 15th ranked datapoint but in 2022 was the 25th ranked datapoint so only the 2021 bar line is showing up when I filter for 20 and below).

Apologies I know this may have been a little long-winded but would greatly appreciate any insight that someone can give to me and my team.

Regards,

Kevin

Jones01

@kcoughlan65 Hi,

I've mocked up some data and produced this using a nested bar chart.

Within the general tab I selected these options which in mind sorts based on the totals of each code and then limits the chart to 10 items. Does this help resolve your issue?

kcoughlan65

@Jones01

Thank you this does somewhat help solve my problem. Ideally i'd love for the shadow of the group bar to be removed from the chart but I think it needs to be there for the original total calculation portion. If there is a way to hide the grouped line in the background that would be amazing, otherwise I might just have to live with it there.

Thanks,

Kevin

Jones01

@kcoughlan65 other than setting the total shadow to fff and turning off gridlines there doesn't seem to be an option for that.

DavidChurchman

There are no rank functions in beastmode (unless I'm mistaken?) but this would be relatively easy to accomplish in Magic ETL.

First, a GROUP BY block, grouping by CPT Code, getting a sum of value.

Second, a RANK & WINDOW block, finding the descending rank of the value sum column you made in your group by.

Third, LEFT JOIN on CPT Code to your original data to add the ranks you just found.

Then you should be able to filter for rank less than or equal to 10.

Using your example data, this would produce this: