How to hide a row, but not exclude the row data from a sumover calculation

[Akash1]

Hello I’m trying to calculate Out of Stock(OOS) percentage for each company in quicksight.
I added calculate field to calculate the percentage

Numerator= sum(OOS GV)
Denominator = sumover(sum(GV),[Company])
perc= Neumerator/Denominator *100
Now I get something like this…

Now I do not have the need to see Instock Perc, so when I exclude Instock, it is also excluded from calculation like this…

I tried using different combinations of PRE-FILTER, PRE_AGG and POST_AGG_FILTER to keep the total value, but it doesn’t work.

Now I cannot find any option to edit this filter of excluding Instock value just for visuals, but not for the calculation. But I also have to have filter calculation values for Year, Month, Country etc.(which are pinned to top on sheet),but I just don’t want to display Instock and consider it for calculation.
I want a similar result(when I add more granularity within the pivot table)

Thank you in Advance

[neelay]

@Akash1 check this link… I think David explained sumover / level aware calculation nicely there…

Level aware sum / sumOver problem

@David_Wong Thanks for the explanation. sum(min(total, [{rule_name}, {event_date}, {channel_id}])) worked on the given example but didn’t work on other data. here is the sample data where it didn’t work, not sure why. Expected output ICD-10 : 93, ICD-9 : 14, FDA Code : 20 total: 127 But, What I got is 86,14,19,119 respectively. [image] The Sample Data can be found here.

Also, check this post. I think David explained sumover solution here.

[Akash1]

Thank you for helping nshah, but I think that thread is not solving my problem, of hiding a column but still considering it for calculation., considering page level filters. I may be wrong, as i am a beginner, but can you suggest the solution in detail please?

[neelay]

@Akash1 , based on the screenshot, looks like you want to hide the rows, is that accurate? If yes… It’s probably not possible. I also don’t see how else it cab be achievable by any BI tool, Unless I still don’t understand your problem…

[Akash1]

Yes, I want to hide the row for instock perc, my team has many dashboards, where we only see the OOS perc and not the instock perc. But I cannot check the architecture of these dashboards, so I thought it would be possible

[duncan]

Hello @Akash1 !

When you add your calculated field to the table does the visual give you an error that you are missing data to create the table?

If so, can you try adding the InStock field and then in the field well options selecting hide?

Let me know if this helps!

[Akash1]

No @duncan , actually the Instock and OutofStock is in the same column, so when I hide Instock values, they are removed from the calculation. I solved this problem by following steps

Numerator=Sum(GV)
Denominator= sumOver({GV},,PRE_AGG)
Rank= ifelse(Inventory=‘instock’, 2,1)
and Perc= Numerator/Denominator

and then I applied a top and bottom filter, to show top 1 values from Rank, this eliminated the instock values from the visualization, but not from calculation.

[Fernanda_Geraldello]

It didn’t work for me, it’s showing a message ‘can’t contain nonaggregated and aggregated field’.

[David_Wong]

Hi @Fernanda_Geraldello,

I suggest creating a separate topic for your specific question so that it gets more visibility. New questions with 0 replies tend to be prioritized.