Hello ,
I have an issue with quicksight dashboard, I want to calculate the HC of the outbound and then divide the number of assosciate in inbound on this number. I want to create a calculated field that shows me the OB headcount even if the department is not OB ( I want this calculated filed to not be affected of the filters I apply and always show the OB headcount)
. and the reason behind that is when I apply a filter for example to IB, I don’t want the value of OB to be disappeared.
I have attached a screenshot with dummy values that may help to understand the idea.
Hi,
if I understand you correctly, you can do this with sumOver and partition by Shift. The value that is aggregated is ifelse(Home Dept = ‘Outbound’,TOTAL HC,0).
So for every Department except Outbound it uses 0, so in the end you get 149. Because we partition by Shift, you get 79 for Day and 70 for Night.
Please test it with your data.
Thomas
1 Like
@thomask Thankyou for your kind replay!
I managed to do it , however I am having an issue as you can see in the picture, the subtotal is not showing after adding the calculated field
Even though I enabled it from the pivot options.
I want the subtotal to show (189/267)=70.7%
Any ideas?!
@thomask Can you please help me?
Hey @khkr999. Thanks for your question. I messaged one of our SAs to see if they can take a look at this tomorrow if you haven’t heard back by then.
@khkr999 - I haven’t replicated your issue of the subtotal not showing. However, with my subtotals showing in the pivot table, the result is additive of the values in that percent column. The QuickSight subtotal doesn’t evaluate 189/267. You can get that value by in a similar approach as described above using sumOver and partitioning by the field name of the ‘Stow to Prime’ field.
1 Like
Hi @khkr999, Did above solution from @Kellie_Burton work for you? I am marking this as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Regards,
Karthik
1 Like