Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Percent in total for a measure which is already a measure in different view

  • 19 January 2019
  • 4 replies
  • 3996 views
L

In my scenario, I need to get sum of Total_Orders group by supplier_name. And at the same time, I need to calculate how much percent in total does this sum of Total_Orders cover for each supplier_name dimension. Can you please help how to achieve.


I am using snowflake database.

The above case when it is written in sql it should act like


select supplier_name,sum(order_total),sum(order_total)/(select sum(order_total) from orders) as order_total_percent from orders group by supplier_name;


But I am not able to get the same scenario in looker. Please help.

4 replies

izzy
Userlevel 7
Badge +1

There’s a few different ways to go about percent of total in Looker-- one is with table calculations, which means you wouldn’t actually need to go int othe lookml/sql. We outline a solid approach here: How to Calculate Percent of Total.


You could also use the percent_of_total measure type: https://docs.looker.com/reference/field-reference/measure-type-reference#percent_of_total which achieves pretty much the same goal, with LookML. The choice is up to you!

L

Thanks Izzy. I got it solved.

G

how about if you apply a filter on supplier name?


Ex: say we have total of 4 suppliers and total of 1000 count

Supp. count. %

sup1 250 25%

sup2. 100 10%

sup3. 300 30%

sup4. 350 35%


this works fine


Now want to apply filter on supplier and still want to see same percentage as above

Filtered Sup2, sup3

Supp. count. %

sup2. 100 10%

sup3. 300 30%


but what is happening is

Supp. count. %

sup2. 100 25%

sup3. 300 75%


how can i solve this?

K

how about if you apply a filter on supplier name?

 

Ex: say we have total of 4 suppliers and total of 1000 count

Supp. count. %

sup1 250 25%

sup2. 100 10%

sup3. 300 30%

sup4. 350 35%

 

 

this works fine

 

 

Now want to apply filter on supplier and still want to see same percentage as above

Filtered Sup2, sup3

Supp. count. %

sup2. 100 10%

sup3. 300 30%

 

 

but what is happening is

Supp. count. %

sup2. 100 25%

sup3. 300 75%

 

 

how can i solve this?

 

same issue

kate d

Reply


Terms of Service