Need to exclude the visual dimension in denominator calculation?

Question & Answer
,
1

Hello Experts, I am new to Quicksight and I am looking for some help in a ratio calculation where I need to exclude dimension from the denominator. Also I have dashboard level filters to count for the output. So basically the ask is only to exclude the dimension at visual level in calculation. Attached sample data and output required for reference. Any help would be appreciated. Thanks.

Sample Data
Sample Data1233×351 38.4 KB

2

Hello @krishna.c, when you say exclude dimension from the denominator, what do you mean exactly? Are you talking about how Zone 3 is being displayed as a zero since it does not have any sales?

If that is the case, is your expected output to exclude Zone 3 from the table entirely or to show Zone 3 as 0/2700?

3

Hi @DylanM, Thanks for your reply. If we consider Zone 1, there are 2 Non-zero records out of 3, so the numerator should be sum of all non-zero values of specific zone(as per the visual break-down) which is 1400 and denominator should be sum of all non-zeros records of all zones which is 2700. Same way for Zone 2, it should be 600/2700 and so on. I tried “(sumIf({Sales},{Sales}>0)) / min(sumOver((ifelse({Sales} > 0, {Sales},0)),,PRE_AGG))”. Kindly advise if this approach is correct?

4

Hello @krishna.c, my thought is to split it up into a few pieces.

Create 4 calculated fields:

  1. ifelse({Sales} > 0, {Sales}, NULL) - I’ll call this {SalesCalc}
  2. sumOver({SalesCalc}, [{Zone}], PRE_AGG) - {ZoneSales}
  3. sumOver({SalesCalc}, [], PRE_AGG) - {TotalSales}
  4. {ZoneSales}/{TotalSales} - This should give you the values you are looking for!

Let me know if this helps!

1 Like
5

Hi @DylanM. Thanks for suggesting the break-down approach. I tried it and getting the same results as in the expression I tried. Thanks again.

6

Hello @krishna.c, are you not wanting to show the Sales for the Zone, but only the sales ratio and it is messing up the calculation? If that is the case you can always include the Sales in the table, but then hide the column from the table to display so it continues to aggregate properly. I might be a little off regarding your expected output, but let me know if this helps!

7

Hi @DylanM, I have verified the numbers with backend and these are the expected results. So the logic is working in both the cases. Thanks.

1 Like