How to get the summation of another column based on the insight of one column

Question & Answer
,
1

Hello everyone,

I’m currently working on customizing a narrative within my QuickSight dashboard and have encountered a challenge. The goal is to ensure that the Cost.metricValue.formattedValue aligns with the same period as identified by Consumption.timeValue.formattedValue. Essentially, the narrative should highlight the cost associated with the month that has the highest aggregate consumption value. However, the issue I’m facing is that the narrative instead shows the month with the highest cost, which isn’t necessarily the same as the month with the highest consumption.

My question is: Is it possible to adjust the calculation to bring in other aggregated values (like the sum of costs) for the period that is selected based on the maximum consumption value?

image

2

Hello @FDelca !

I would try adding a the following computation based on your consumption field. Then ordering by month with the formatted date value:

Let me know if this is what you were looking for!

1 Like
3

I am already using that - what I need is to bring the cost metric that is the aggregation of the month selected by the max consumption. Can you provide me an example or even the code so I am able to replicate the solution using order?

4

Hello @FDelca, so what I have determined for this is you will need to use calculated fields to determine the month that will contain the max consumption value in the insight. Then, you can use that month value to return the total cost for the month associated. That calculated field will then be added into your insight and you can add a computation for Total Aggregation on that field. I’ll post the steps below:
Month with Max Consumption = ifelse(maxOver(maxOver({Consumption}, [truncDate('MM', {Date})], PRE_AGG), [], PRE_AGG) = maxOver({Consumption}, [truncDate('MM', {Date})], PRE_AGG), truncDate('MM', {Date}), NULL)

Sum of Costs for Max Month = sumOver({Costs}, [{Month with Max Consumption}], PRE_AGG)

Add that calculated field to your field well, then edit the narrative and select Add computation:
image

Select Total Aggregation:
image

In the value dropdown select the Sum of Costs for Max Month calculated field. Then you can utilize this value TotalAggregate.totalAggregate.formattedValue in your narrative to grab the total cost. This should give you the desired solution. If you have any further questions, please let me know!