# Count all products with True values and show % of total **URL:** \ **Category:** Kibana\ **Created:** [February 24, 2022, 1:07pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163 "2022-02-24T13:07:12Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Utkarsh\_Khodke](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/utkarsh_khodke/32/102274_2.png) [@Utkarsh\_Khodke](https://discuss.elastic.co/u/Utkarsh_Khodke)\ **Post date:** [February 24, 2022, 1:07pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/1 "2022-02-24T13:07:12Z")
Hello, I'm new to Elasticsearch and Kibana and have a fairly basic question. In the data loaded in Kibana, I have store\_id and the number of products sold in each store. I also have a field call payment\_status which is a boolean and shows true if payment has been made and false if payment is pending. I'm trying to create a simple table visualisation that shows the top N stores with highest number of products sold and the % of payment status as True (let's call it payment\_rate) for each store. I researched a bit and found out that scripted fields is not the way to go about doing something like this as scripted fields do not accommodate for aggregated fields (count of store\_id in our case). I come from a background of Tableau and creating scripted fields was the first thought as it's closed to creating calculating fields on Tableau. How can I achieve a viz like this in Kibana? Please note: Field names have been edited as this is for work. Attached view of the data table: ![Screenshot 2022-02-24 181009](https://us1.discourse-cdn.com/elastic/original/3X/3/c/3c1605b842b02c92a755e5185611d0f69fa392eb.png) ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [February 24, 2022, 2:30pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/2 "2022-02-24T14:30:04Z")
With lens, you can use formula: ```auto sum('count',kql='payment_status:true')/sum('count') ``` to show payment\_rate: ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/4/2432f01079b672f0c98bff5961e91f0a5f60b7ce.png) But one difficulty is the order of store\_id is limited to " **Alphabetical**", at least in my 7.16. Using "aggregation-based", you can use custom metric to sort sotre\_id, but is is not still implemented in lens. I'm not sure it is implemented in 8.0, but I hope it will in near future. On the other hand, I found no way to calculate the rate in aggregation based data table. ---
**Author:** ![ghudgins](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ghudgins/32/138532_2.png) [@ghudgins](https://discuss.elastic.co/u/ghudgins)\ **Post date:** [February 24, 2022, 2:43pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/3 "2022-02-24T14:43:17Z")
For what it's worth, we currently have it on our 8.x plan to address the sorting limitations with custom formula - [[Lens] Allow client-side sorting of dimensions and legends at datasource level for all chart types · Issue #86184 · elastic/kibana · GitHub](https://github.com/elastic/kibana/issues/86184) ---
**Author:** ![Utkarsh\_Khodke](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/utkarsh_khodke/32/102274_2.png) [@Utkarsh\_Khodke](https://discuss.elastic.co/u/Utkarsh_Khodke)\ **Post date:** [February 25, 2022, 9:24am UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/4 "2022-02-25T09:24:07Z")
Thanks for your reply Tomohiro and Graham! Unfortunately, I don't see an option called 'lens' in my visualisation tab. When I click on çreate new visualisation' I can see many options but no option named lens. I'm not sure if it's because I'm on some different version or if my organisation hasn't 'enabled' the option. I'm on v7.8.0 Once again, apologies for these very basic questions as I'm new to Kibana and Elasticsearch **Edit: Just realised Lens is only available for versions 7.10 and above. link:** [Kibana Lens is now generally available | Elastic Blog](https://www.elastic.co/blog/kibana-lens-now-generally-available) Is there anyway I can do this without using Lens? ---
**Author:** ![ghudgins](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ghudgins/32/138532_2.png) [@ghudgins](https://discuss.elastic.co/u/ghudgins)\ **Post date:** [February 25, 2022, 3:44pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/5 "2022-02-25T15:44:05Z")
Try the "Filter ratio" option in TSVB. Here you can provide KQL for the numerator and denominator to return a % value. Should be in the version of Kibana you're on.... (or you could upgrade 🚀 😉 ) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 25, 2022, 3:44pm UTC](https://discuss.elastic.co/t/count-all-products-with-true-values-and-show-of-total/298163/6 "2022-03-25T15:44:59Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.