Percentage of Total by field

[Bharat_kumar]

Hi Guys, i have a requirement to do a percent_of_total division wise but the data has multiple divisions.

Here is the data

Division	Order Type	Count
Division1	Flex	20
Division1	Non Flex	30
Division2	Flex	10
Division2	Non Flex	50

What i have now is this below by using the below lookml

measure: division_percent_of_total {
type: percent_of_total
sql: ${count} ;;
}

Division	Order Type	Count	Percent_Of_Total
Division1	Flex	20	18%
Division1	Non Flex	30	27%
Division2	Flex	10	9
Division2	Non Flex	50	45%

But what i need is this to be able to get the percent of total for each division and have all the divisions in the dashboard. So here the percentage for row 1 is 20/(20+30) instead of 20/(20+30+10+50). Can you please help me if i can do like this? Any help is greatly appreciated

@izzymiller

[cole_elliott]

Hi @Bharat_kumar 👋

Assuming the number of rows isn’t going to surpass row limit constraints, I think you could get the output you’re wanting by adding in a Table Calculation that divides each Count by the sum of all of the Count values.

[Bharat_kumar]

Hey @cole_elliott : it should not be sum of all count values… it has to sum for all the values of only specific division

[cole_elliott]

Ah, I see.

OK, in that case, I think if you pivot then by Division, then you will get what you want, which if I’m understanding correctly, is a percentage of the total by Order Type per Division.

[Bharat_kumar]

Perfect @cole_elliott … that worked… Thanks much…

[cole_elliott]

Sure thing @Bharat_kumar - glad that worked 👍