CODEWITHSUNDEEP
excelpivot
Kbbm
Kbbm

Reputation: 375

Distinct sum in excel/sheets pivot table

Let's say I have some data I generated through a window function (lifetime_clicks)

I want to know in excel or google sheets pivot table if can get the % of lifetime clicks come from today_clicks (hour_clicks / lifetime_clicks)

user_id date hour_clicks lifetime_clicks
     1   02      30            90000
     1   02      2             90000
     1.  02      200           90000
     1.  03      4544          90000

I would like to group the data on date and sum(hour_clicks) and divide that by 90000, but everytime I enter lifetime_clicks to the calculated field, it sums the data.

is there a way to distinct sum(lifetime_clicks) to prevent such a thing from occurring?

Upvotes: 0

Views: 111

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

enter image description here

I would like to group the data on date and sum(hour_clicks) and divide that by 90000

Easiest option is adding a HELPER column, like my column E in image above. Formula is just =C2/D2

Second option, if you are going to divide always by 90.000, then you could use a Calculated field.

enter image description here

To be honest, I think easiest way would be helper column.

Upvotes: 1

Related Questions