Create a gauge graph with dynamic axis maximum

[fperdomo]

Hey hello!
I'm searching for a way to visualize the amount of quota used by a service, could be a bar graph but gauge works as well.
The data I'm working with has the following format:
service_name, total_quota, used_quota

The total quota is different in every service, so if I want to visualize the amount of used quota, I would need to set that the max value is the total_quota field. However, if I go to Style > Axis > Max Axis I only have the option of choosing a static number. How could I solve this?

In this example screenshot this service has a quota of 89 and I set it up manually, but if I select other Service in the dropdown list, the Axis max stays 89, but the total might be a totally different number

[ArkadyZagdan · Accepted solution]

@fperdomo 
The chart you have selected allows you to add fixed target only.
Try going with bar chart instead. Having a data structure like this:

You can  create additional  formula to compute Quota less ccurrent usage.

And create a stacked chart showing labels for totals (this is your quota i assume)

Heres report with this chart:
https://lookerstudio.google.com/u/0/reporting/fe93db86-5a57-4d70-87cc-3703ec7dc31e/page/k91ZE/edit

Other thing is I would probably show as percentages in  this case just to keep more comparable.

Regards
Arkady Zagdan

Bigglo.pl

[fperdomo]

I see what you did there! This can work for me, but I realized I have to restructure my data, my actual tables are not exactly like that, they look more like:

client_name, actual_dts, limit_dts, actual_networking, limit_networking, actual_service1, limit_service1,.. etc
client1, 1000, 2500, 300, 1000, 123,123 ...

All the information is flattened into a row, I would need to normalize service_name, actual and limit as columns.
Unless you know a way of working with the data as I presented, but I think the most rational way is restructuring my tables

[ArkadyZagdan]

@fperdomo 
I see. In your case, you would need to create new calculated field for each service. 
LS1-AS1
LS2-AS2
etc
So I guess its easier to have it pivoted but I am not sure how  many services in total you are looking at:)

[fperdomo]

a lot