Sum Over DistinctCount with Different Agg Levels

Question & Answer
,
1

I am trying to calculate the cost of different job types over the total number of operations over a date range. For this, I have a date_granularity calculated field that is truncating the date part of the depending on the user choice date_parameter:

ifelse(
    ${granularity}='Quarter',truncDate('Q',{date}),
    ${granularity}='Month',truncDate('MM',{date}),
    ${granularity}='Week',truncDate('WK',{date}), 
    truncDate('DD',{date})
)

The cost field is calculated as follows:

sum(
    {job_cost},
    [{date_granularity}, {job_type}]
)

The distinct count uses the same calculated field for the partitions and is called num_operations_in_window:

distinct_count(
    {operation_id},
    [{date_granularity}]
)

When I try to divide the two fields:
{job_type_cost}/{num_operations_in_window}

I get the error:

The Level Aware Calculation (LAC) aggregate functions inside one visual aggregate functions should always share the same grouping key

Is there some workaround to this? I just want to divide those two fields (first one is job_type_cost and the second one is num_operations_in_window):
Screenshot 2023-10-10 at 11.17.01 AM

2

Hi @juan_camilo_puello , welcome to the QuickSight community!

I see your numerator that is {job_type_cost} has both {date_granularity}, {job_type} in the group by and only {date_granularity} for {num_operations_in_window} calculation or the denominator. Is that how you want your final metric to be calculated?

3

Hey @salim ! Yes, the requirement is to take the whole number of operations in that window even if the job was applied on less operations.