13 Replies Latest reply on Jan 31, 2018 1:56 PM by Kimberly Goldstein

How to compare two calculated fields?

Hi there,

I recently learned how to create weighted averages in Tableau and am trying to compare weighted averages within one group to the overall weighted average of a broader set (including all members of that group).

I've created a sample - attached - where I have a weighted average value for each "Parent" that represents the weighted average revenue (weighted by the number of visitors at each company within the Parent). The weighted average revenue figure updates when I filter on specific regions (intentionally). I have also created a multi-region ("market") benchmark, which is the weighted average revenue for all companies in that market (which flexes with the region filter).

I am trying to now calculate the difference of each "parent" relative to the multi-region/market benchmark. For example, in the current dashboard view, Parent "Blue" has a weighted avg. revenue of \$108 and across the five regions. This is 8% above the benchmark. If you remove region 2 from the multi-region area, Parent Blue's weighted avg. goes up to \$125 and the benchmark only goes up to \$104, so the relative revenue of parent Blue to the market average is greater than it was before.

Is there a way to create this dynamic value that represents the relative advantage / disadvantage of a given parent compared to the market average, that can change based on which regions are included/excluded in the market?

Thank you so much!!

• 1. Re: How to compare two calculated fields?

Kimberly,

This very likely can be simplified, but mostly wanted to just propose the idea.

I think it may be best to use Level of Detail calculations to fix the benchmark value

for use in comparisons.

I broke it down into three parts, but there may be ways to condense this.

The Numerator Benchmark Product is:

{ FIXED [Company]:SUM([Revenue]*[Visitors])}

The Denominator Visitor Total is:

{ FIXED [Company]:SUM([Visitors])}

The Benchmark is then the quotient of the two:

{SUM([BenchmarkProd])/SUM([VisitorTotal])}

This can then be used for the difference:

[Weighted Avg. Revenue]-SUM([WeightedRevenue LOD])

Of note, since it needs to flex with [Region],

the Region pill on the filter was added to the context

1 of 1 people found this helpful
• 2. Re: How to compare two calculated fields?

Hi Swaroop,

I tried applying these formulas to my actual data and am running into an issue: when I change the region, the Weighted LOD value does not change (the weighted avg. revenue and difference values change). Any idea what the issue might be?

Kimberly

• 3. Re: How to compare two calculated fields?

Kimberly,

Would be grateful if you could post a screenshot (redacted as needed) of your worksheet

including the pills on the Filters shelf.

The color of the [Region] pill on the Filters shelf should be grey,

indicating that it has been added to the context.

If it is grey and has been added to the context,

for the Weighted LOD and the Revenue*Visitor product, if you are using that.

1 of 1 people found this helpful
• 4. Re: How to compare two calculated fields?

Hi,

Thank you. That was the issue.

If I want to show the difference as a % of the benchmark, do you have guidance on how to do that? (Ideally, would want to show that Parent A has X% more or less revenue than the regional benchmark).

Thanks again,

Kimberly

• 5. Re: How to compare two calculated fields?

Kimberly,

I think that should be feasible with a calculated field of

( [Weighted Avg. Revenue] - SUM( [WeightedRevenue LOD] ) ) / SUM( [WeightedRevenue LOD] )

1 of 1 people found this helpful
• 6. Re: How to compare two calculated fields?

Hi there,

That worked! Thank you for all your help.

• 7. Re: How to compare two calculated fields?

Kimberly,

All the best.

• 8. Re: How to compare two calculated fields?

Hi Swaroop,

I have another question along a similar vein. If I wanted to create a dynamic metric to show the market share each Parent based on their relative number of visitors in a given region (or multiple regions), how would I go about doing this?

I tried doing a similar LOD calculation using fixed for region and then visitors, but that didn't work. Thanks!!

• 10. Re: How to compare two calculated fields?

Kimberly,

I made an attempt, but not sure if I quite caught the specifications.

I tried to take all of the Weighted Avg. Revenues, add them up,

and then see what percentage of the total each Parent contributed.

The same style of LOD was used for calculating each Parent's Weighted Revenue [WeightedRevenue LOD Company]:

SUM({ FIXED [Company]:SUM([Revenue]*[Visitors])}) / SUM({ FIXED [Company]:SUM([Visitors])})

Then Total of the Parents was [TotalWeightedRevenue]:

WINDOW_SUM([WeightedRevenue LOD Company])

So I tried to calculate a MarketShare as the quotient of those two:

[WeightedRevenue LOD Company]/[TotalWeightedRevenue]

This should be responsive to [Region].

If this is not correct, please post a definition for Market Share,

and a table of what the expected percentages should be.

• 11. Re: How to compare two calculated fields?

Hi there,

I've attached your workbook with an attempt to calculate the market share. It is not there quite yet.

Right now, I defined the total number of visitors across each region and then divided the number of visitors for each company (or parent) by the RegionVisitors field. This now tells me the share each company or parent has within a given region.

I am trying to calculate the share of each company or parent relative to the sum of visitors across multiple regions (in the current view of my workbook - attached - there are 485 visitors across the companies selected and regions selected. I'd like to calculate the Parent Market share based on this total, not the individual regional total. Does that make sense?

Thanks,

Kimberly

• 12. Re: How to compare two calculated fields?

Kimberly,

Please see if the below is closer.

I think it may be accomplished in this way:

[Visitors per Company]:

{ FIXED [Company]:SUM([Visitors])}

[Visitors per Parent]:

{ FIXED [Parent]:SUM([Visitors])}

This may be a bit obscure: the Total number of visitors

given the current selections of [Region] and [Company]:

[Visitors Total in Context]:

{SUM([Visitors])}

This LOD calculation has no dimensions on the left side of the colon, so it is returning the total sum of visitors,

given the selections made of the filters that have been added to the context.

Then the Marketshare for a Company:

[Visitors per Company]/[Visitors Total in Context]

and Marketshare for a Parent:

[Visitors per Parent]/[Visitors Total in Context]

Please see the sheet MarketShare_KG2 in the workbook attached.

1 of 1 people found this helpful
• 13. Re: How to compare two calculated fields?

That worked! Thank you!!