Finding the Top id by value

Question & Answer
,
1

I am looking to get the id of max value in a column. For example, top service by spend or top spending account in CID - https://d1s0yx3p3y3rah.cloudfront.net/anonymous-embed?dashboard=cid

I am able to get the max value using the maxover function but need help to locate the id for the max value

2

Hi @Arvind_Shastry - can you explore an option to put a condition if clause on max over… I will explore on this coming week and update you. By saying that this is an interesting use case and thanks for posting this question.

Hi @Max @David_Wong - Any expert advise from you on this.

Regards - Sanjeeb

3

Hi Sanjeeb,

I attempted to write the if clause for the maxover clause but could not succeed. Could you directionally share how this would look like

have monthly billing data for 3 years for Account Name, Service name, unblended cost all under a single master payer. Objective is to identify the max. spend account and highest spend service this month. I am able to get the max value for this month using max over on the aggregate spend per service (done using sum over)

Note that This Month and Last month spend are calculated measures. Sample data schema looks like this

image

Same output looks like this

This Month

Service with highest spend - Savings Plan
Account with highest spend - ABC

Last Month

Service with highest spend - Compute
Account with highest spend - XYZ

1 Like
4

Hi @Arvind_Shastry - Thanks for providing the sample data. I will have a look on this coming week and update you.

Tagging @Biswajit_1993 and @David_Wong , @Max for their quick advise on this as well.

Have a great weekend.

Regards - Sanjeeb

5

I would first calculate a total cost by account and by service.
Total Cost by Account = sumOver({Unblended cost}, [{Year_month}, {Account Name}], PRE_AGG)
Total Cost by Service = sumOver({Unblended cost}, [{Year_month}, {Service Name}], PRE_AGG)

I would then calculate a rank based on the total cost in descending order.
Account Rank = denseRank([{Total Cost by Account} DESC], [Year, month], PRE_AGG)
Service Rank = denseRank([{Total Cost by Service} DESC], [Year, month], PRE_AGG)

Lastly filter by this month/last month and rank = 1.

2 Likes
6

Thanks David. I managed to get the service rank. I am wondering which visual type will allow me to show the top service by spend (as shown in the Cost Summary tab on CID). All visual types only show the data. example below (using the pivot table)

servicerank

7

If you just want to display the service name and nothing else, you can add a “Top ranked” insight and customize the narrative to only have the service name. It will look like this:

image
image856Ă—309 14.4 KB

1 Like
8

Thanks. Unfortunately, i do not see the option to add an insight to my quicksight dashboard. I am using Quicksight standard edition on a free tier account with Quicksight managed IAM role.

9

Insights are only available in the Enterprise edition. You can try to use a flat table as a workaround. Add a table with only one column - Service Name. Hide the table header and change the cell background and border to make it look like KPI.
image

1 Like
10

Thanks, David. I did use this approach and it worked.

2 Likes
11

Hi @Arvind_Shastry - Can you please marked David’s suggestion as solution so that it can help to wider community.

As usual thank you so much @David_Wong

Regards - Sanjeeb

1 Like