Hide a field and total based on a new field with no duplicates

[Chloe_G]

Hi Looker community,

I mainly use the front end of looker and try not to edit the LookML so am looking for a solution to the below which can be done using custom dimensions or table calculations.

I have 3 fields. The first is the ‘account name’ which contains unique values. The second is an assigned value within the set {10,50,90} therefore these values appear more than once. The third field is a revenue sum measure.

When I hide ‘account name’ from the visualisation, it literally just hides this field and does not total unique values in field 2. (I would like to have 3 rows {10,50,90} and their total revenues) The reason why I do not want to remove ‘account name’ is that I plan to do a merge using this field and then some table calculations to adjust the revenue values.

Thanks!
Chloe