Show total despite using 'group by' field in Table visual

Q&A
,
1

Hi
I’m trying to calculated a grand total number named - ‘All’.
The table visual would be grouped by ‘SOURCE’ column.
My goal is to show the same number, irrespective of the ‘SOURCE’ group by dimension.
I tried different logics but all failed.

In order to calculated the ‘All’ column I first need to calculate this:
max({IS_ENCOUNTERS}, [NID, ICD])
then, i need to plug it with an outer function to perform the sum calculation.
however, I’m familiar with LAA and I know I need to use this - doesn’t matter which combination of functions i used I still got the ‘All’ total per source and not the desired total.

I tried these:
sum(
max({IS_ENCOUNTERS}, [NID, ICD])
)

I also tried with sumOver and maxOver and it didn’t work.

Waiting for your help
Thanks

2

Hi @tomer1 ,
Try this:
First, create a calculated field for the inner max by [NID, ICD], as you’ve been doing
max({IS_ENCOUNTERS}, [{NID}, {ICD}])
You can give it a name e.g MaxCountbyNID_ICD

Then:
you want to sum those max values across ALL data, ignoring the ‘SOURCE’ groupings

Calculated field for Grand Total:
sumOver(
{MaxCountbyNID_ICD},
[ ],
PRE_AGG
)

3

Thanks, I will check this soon and will reply here if that works.
Any way, can you please explain the reasoning for this behavior and why this logic should work?
Thanks

4

hi @tkinoti I examined and validated your suggestion and got this error when using sumOver:

Level Aware Calculation (LAC) agg functions can not be used as operands of LAC window functions.

I guess it because it’s still sumOver with nested max with is invalid in Quicksight.

Do you have another workaround?

5

Hi @tomer1 ,
Are you able to calculate the max in your data layer before quicksight?

6

that’s not possible :no_entry:

7

Hi @tomer1,

I hope everything is well! Just wanted to check in as I saw your recent post has not been answered yet. Were you able to find a workaround for what you are trying to do or are you still running into persistent issues? If there is no response within 3 business days, I’ll close this topic.

8

Hi @tomer1,

Since we have not heard back, I will close out of this topic. However, if you have any additional questions, please feel free to create a new post in the community and link this discussion for relevant information if needed.

Thanks!