How to Calculate Percentage of Resources for the Latest Period in AWS QuickSight?

Hello AWS QuickSight Community,

I am working on an analysis in AWS QuickSight where I have a dataset containing the following fields: resourceId, periodId, periodwithdate, department, industry, sector, and levelgroup.

I have created a horizontal bar graph with levelgroup on the Y-axis and the percentage of resources on the X-axis. To calculate the percentage, I am using the following calculated field : distinctCountOver({res_id}, [levelGroup], PRE_AGG) / distinctCountOver({res_id}, , PRE_AGG). Additionally, I have created parameters for periodwithdate, department, industry, and sector fields. These parameters are used in filters, and I have added controls in the UI to allow users to select the required period, industry, offering, etc.

The issue I am facing is that when users select multiple periods, the percentage calculation considers all the selected periods. However, I only want to calculate the percentage for the latest period selected by the user. For example, if the user selects the following periods in the UI:

  • FY25 P1 (6/2-6/29)
  • FY25 P2 (6/30-7/27)
  • FY25 P3 (7/28-8/10)

The calculation should only consider the data for FY25 P3.

I have tried creating calculated fields to identify the latest period and filter the data accordingly, but it is not working as expected. Here are the steps I followed: step 1 . Created a calculated field to identify the latest period:

latest_period = maxOver({periodId}, , PRE_AGG)
step 2. Created a calculated field to check if the current period is the latest period: is_latest_period = ifelse({periodId} = {latest_period}, 1, 0)
step 3. Created the percentage calculation for the latest period: distinct_count_latest_period = ifelse(
{is_latest_period} = 1,
distinctCountOver({resourceId}, [levelGroup], PRE_AGG) / distinctCountOver({resourceId}, , PRE_AGG),
0
), Now the value which is coming is zero.

Could someone please guide me

Hi @abhjaiswal,
I’d be interested to know if you tried running your steps individually to make sure they were operating correctly?
For instance, your calc. field in Step 2; have you tested to see if it returns ‘1’ or if just receiving ‘0’s’. It could be possible that would be impeding step 3 from producing any results.

Additionally, if you are able to upload an anonymized copy of your analysis to QuickSight Arena, it may be easier to assist further so that I can test out some alterations.

1 Like

Hi Brett, you were right ! now I have included the filter for is_latest_period and sets its value as 1 so I am not getting zero anymore, however the values which are coming now are not of the latest period it is considering all the periods I have selected. Can you please further help me on this ?

Hi @abhjaiswal,
My thoughts are that it has something to do with your step 3 calculation for this issue. When running your distinctCountOver, you’ll want to include the latest period in your partition along with ‘LevelGroup’. That way it’s specifically doing a distinct count for the desired period.