CODEWITHSUNDEEP
visualizationtableau-desktop
Katsu
Katsu

Reputation: 8925

Tableau: Convert Stacked Bar Chart with Multiple "Measure Values" to a normalized 100% Stacked Bar Chart

This is what my current view looks like.

Right now my Tableau chart shows the breakdown of average fare, Grouped By different Community Numbers.

enter image description here

I would like to also show a version of this chart that normalized all these stacked bars into 100% total as the maximum. So most of these that are almost all teal should have the "Avg Fare" teal be ~90-100% of the bar in their respective Community Number. Some of them, like Community #76, should have like 75% of the bar be contributed by "Avg Fare", 20% of the bar be "Avg Extras", and 5% of the bar be "Avg Tips".

The Talbeau forums say that you have to do AVG( [Extras] ) / (AVG([Extras])+AVG([Tips])+AVG([Tolls])+AVG([Fare])) for each individual Measure Name but I refuse to believe there isn't an easier way. Thanks.

Upvotes: 1

Views: 550

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11921

There is a somewhat easier way, but conceptually equivalent.

I recommend simplifying by defining a calculated field called, say Trip_Cost, as

[Fare] + [Tolls] + [Tip] + [Extras]

Then could define a series of calculated fields called, say Tip Percentage, as [Tip] / [Trip_Cost] and Fare Percentage as [Fare] / [Trip Cost]

Right click on the Tip Percentage field and its kin in the data pane in the left margin, and set the default properties, such as make the default number format a percentage, and the default aggregation average instead of sum. You can do that for several fields at once by multiple selection.

Now you can easily work with your Percentage fields or the original (currency valued) ones to make the calculations or visualizations you want.

If you not using the Measure Values shelf, then Tableau has quick table calculations that make calculating percentages very easy - without requiring these relatively simple calculated fields.

EDIT - The above works if you are always aggregating percentages using AVG() for aggregation, but is a bit brittle in other circumstances. An alterative is to define the Tip Percentage style calculations as aggregate calculations as SUM([Tip]) / SUM([Trip Cost]) This ensures that someone doesn't accidentally use the wrong aggregation function and get a strange result. Its a more typical approach - the classic example is computing Profit Percentages.

Upvotes: 1

Related Questions