Kibana visualization

[sai_ravi_shankar (sai shankar)]

Hi

I am trying to build a visualization. Here is the scenario.

i have two coulmns, lets say company_name and cost_paid_by_company. i need to show the cost_paid_by_company which is higher and lower than the threshold in any kind of visualization

Data:
company_name | cost_paid_by_company
A | 120
B | 100
A | 50
C | 70
C | 20

Sorting with sum_cost_paid_by_company:

company_name | sum_cost_paid_by_company
A | 170
B | 100
C | 90

Now threshold is 95

Company's with higher than thresold = 66.6%
Company's with lower than thresold = 33.3%

How can i represent this in any kind of visualization for Company's with higher than thresold and Company's with lower than thresold ?

[Marco_Liberati]

Hi @sai_ravi_shankar

I think you can visualize the final percentage using Lens formula to compute your values:

• Create a Percentage chart (either horizontal or vertical)
• Define a Top values of company_name as Breakdown by dimension, and apply the Collapse by Sum to it
• Then define two Metric dimensions as follow:
  • First formula would be: ifelse(sum(cost_paid_by_company) > 95, 1, 0) / overall_sum(ifelse(sum(cost_paid_by_company) > 0, 1, 0)) and label it Companies higher than threshold
  • Next a similar formula but for companies with lower cost: ifelse(sum(cost_paid_by_company) <= 95, 1, 0) / overall_sum(ifelse(sum(cost_paid_by_company) > 0, 1, 0)) and label it Companies lower than threshold

To explain a bit the formula, I'll break it down in two pieces:

• First part is counting the companies who paid more than the threshold

  ifelse(sum(cost_paid_by_company) > 95, 1, 0) 
  

• And this will be divided by the sum of all the companies who had any cost paid (>0):

overall_sum(ifelse(sum(cost_paid_by_company) > 0, 1, 0))

Note that the overall_sum will compute the sum beyond the Breakdown by and count all the companies, hence the final percentage ratio.

Here's an example I built who the Ecommerce sample dataset. I used category.keyword (as your company_name) and sum(products.base_price) (as your sum(cost_paid...)) with a threshold of $3000:

If what you want instead is know which company passed the threshold and which not, maybe a table can be helpful with some custom coloring: