Help with measure error in aggregation workaround for bar chart

Question & Answer
,
1

I need to show dollar amount by pipeline status in a bar chart. I also need to add another measure, estimated open & close, to compare it in the same visual. The estimated open & close is an aggregated measure that can’t be broken down by pipeline status.

My workaround is to create a few new measures - one measure counts all of the records that are in the estimated open & close, one measure gives the counts of all records by pipeline status and also in estimated create and close.

I need a third measure to give me the sum by the above second measure. The measure I built is giving me an error where nested aggregations can’t be included. Here is the measure I tried:
sum(
ifelse(
Flag2 = “previously closed”,
{Remaining Estimated Create & Close}
, NULL
)
)
Does anybody have a suggestion of another way to get the dollar amount?

image
image1164×583 16.1 KB

2

Here’s a solution that could address both requirements: showing dollar amounts by pipeline status and displaying an aggregated “estimated open & close” measure in the same visual. Since the challenge lies in aggregating values while keeping the comparison of both, we can work around this by creating measures that don’t nest aggregations but still let us perform the calculations.

  1. Pipeline Status Dollar Amounts: First, calculate the dollar amount by pipeline status as a standalone measure (if it’s not already available). This will give you the dollar value associated with each status.

  2. Estimated Open & Close as a Separate Measure: Since “estimated open & close” is aggregated and cannot be split by pipeline status, create this as a separate measure, which will remain constant across all pipeline statuses.

  3. Avoiding Nested Aggregations: Since your initial measure causes an error due to nested aggregations, you can try creating a measure that isolates the logic of Flag2 separately, allowing you to use a different approach in the main measure. Here’s a potential workaround:
    FlaggedAmount =
    IF(Flag2 = “previously closed”, [Remaining Estimated Create & Close], 0)
    TotalFlaggedAmount =
    SUMX(YourTable, [FlaggedAmount])

  4. Bar Chart Setup: Now, plot both [Pipeline Status Dollar Amount] and [Estimated Open & Close] on the same bar chart. Since the “Estimated Open & Close” won’t break down by pipeline status, it will appear as a single bar (or line, if preferred), while the pipeline status amounts show individually

3

Thanks for the quick and thoughtful response. This approach didn’t work, unfortunately. There is no If function in Quicksight, but I tried both elseif and sumif arguments in quicksight for to calculate FlaggedAmount and neither solution worked. Issues with nesting and or aggregation because of the Remaining Estimated Create & Close measure.

I should say I already have another measure to aggregate dollar amount. I use the existing measure to aggregate by several fields, one of them being pipeline. But pipeline isn’t explicitly referenced in the measure.

4

Hi @jonfrank3366,

Can you share your calculated field for {Remaining Estimated Create & Close}?

5

Sure thing. Here is the measure:
Remaining Estimated Create & Close = {Total NB Bookings ARR} *
max(
ifelse(truncDate(‘WK’, {COALESCED_DATE}) = truncDate(‘WK’, ${52weeksago}), {Curr_week_multiplier}, NULL)
)

Curr_week_multiplier is another calculated measure. Here is the formula = minOver({WEEK_MULTIPLIER}, [truncDate(‘WK’, {COALESCED_DATE})], PRE_FILTER)

Basically I built this to look at historical data and then use a multiplier based on the week of the quarter.

6

Hi @jonfrank3366,
It’s been awhile since last communication took place on this post, are you still encountering the same issues or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

7

Hi @jonfrank3366,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!