Create KPI field that shows hourly average of record count based on another field

Hi all!
I have the following issue (?) in Quicksight and I was wondering if maybe some of you have come across something similar in the past and have a suggestion how to tackle it.

I am trying to create a KPI card (or a calc visual, just not a table) that would show shutdown ratio of the average amount of hours spent during business hours and after hours. This is how I would do that in Excel:

  1. Get the average of amount of hours for Business Hours (col. H)
  2. Get the average of amount of hours for After Hours (col. I)
  3. Calculate the ratio: 1-AVG(BusinessHours)/AVG(AfterHours) and show as % (col. J)

I found this article, but a) it includes table calculations (which I am not after as I would like to have a KPI card only), and b) I have another field based on which I would like to distinguish the ratio (Business/After hours) so it does not help much in the end. // Edit: cannot attach the link due to the embedded media items limit for the new users. Here is the title posted by Karthik1: “Create KPI field that shows hourly average of record count”

The closets I got to is to have the correct ratio %, but it appears as a negative value (Difference as %)

*Please note that the values in Excel and QS are different as in Excel I used dummy data.

My dataset has multiple fields, but for this calculation purpose I used only a few, so I started with the Shutdown Ratio LAC calc:

sum({usage_amount},[environment, domain, linkedacctname, {line_item_usage_start_date_Str}])

Then I created a KPI card and it works perfectly fine except the fact that it’s showing a negative value (in this case 32.65/43 = 76%, so the calculation shows -24% as a difference as % and I would like to see the absolute value/% which would be 24%)
BH/AH Calc is just a calculation to assign the string values “Business Hours” or “After Hours” to false/true values in the data.

// Apologies, I cannot post a screenshot of the KPI in this post due to the embedded media items limit for the new users. I replied in this thread with the screenshot. Anyway, I put the Shutdown ratio (aggregated to show Average) calc to Value and BH/AH Calc to Trend group. selected Difference as percent % option in Comparison method and got -24% as a result.

I hope this explanation it clear and you guys understand what I am after. Any ideas and suggestion will be much appreciated!

Thank you
Iwona

Here is the KPI card:

Hi @Iwona_M

I was able to create a calculated field using the same formula you use in Excel
image

I could then use that in the KPI (with or without the date in the trend group)

Does this help?

The other thing which could be nice would be to create an insight

To do this you can create a new insight and then customise it. You’ll need to add computations for the three value fields (I used Total Aggregation). You can then create a meaningful narrative for your data. (note, you’ll need to remember to set the values to Average in your field wells before you start customising)

2 Likes

Hi @Steph,

thanks so much for taking your time to investigate my problem and replying. Thank you for the Insight box suggestion, I use that very often as well :slight_smile:
In terms of the KPI calculation unfortunately this is not it yet. As mentioned in my (long) post, I have multiple fields in my dataset, so I need to use the LAC expression to get the expected level of detail, I also would like to see the ratio on hourly basis, thus you can see Usage Start date is converted to String (so it’s not only a short date, but a full long date time. That would be my ‘AH’ (after hours) field.


I would create a similar calculation for ‘BH’ (business hours) and just then would create a Ratio calculation. Unfortunately when I have these fields separated, the AVG numbers and the Ratio in KPI card does not give me the correct results and now I’ve figured out the way why. Turned out that Quicksight replaces blanks with 0, so it results in incorrect AVG value. But at least I need to fix this one now and I should be good :slight_smile: If you have any idea how to do it, I would appreciate it as well :slightly_smiling_face: This is the example from Excel what I mean:

image

Thank you again for your time!
Iwona

Ok, I’ve figured out the whole thing :slight_smile: It works just fine as a simple calculation, KPI card is not needed. I removed zeros using nullIf function and now the AVG calculation shows the correct result.

Fantastic! I was also going to suggest using a filter, but glad you found a solution.

That’s a good tip, did not know this solution. Thank you! :smiling_face: