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 ?

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:

```
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: