9 Replies Latest reply on Jan 15, 2017 9:56 PM by Khalid Baker

# How can I determine good or bad %s for multiple measures?(Colour code)

Hi everyone;

I have an issue determining good from bad as colours. Suppose we are looking at an income statement and creating formulas in excel to look at certain expenses as a % of Net Revenue.

Suppose I have General & Administrative, Sales Cost, Sales & Distribution as well as Advertising and Marketing expenses each as a % of Net Revenue. Example: General & Administrative/Net Revenue %= 24% for a region1& its 30% for region2, whilst Sales Cost/Net Revenue % = 35% for region 1 & it is 25% for region2.

To find out what is acceptable or not, we use the following:

1) For General & Administrative/Net Revenue %, we would say below average General & Administrative/Net Revenue % is acceptable & above average General & Administrative/Net Revenue % would be unacceptable.

2) For Sales Cost/Net Revenue %, we would say below average Sales Cost/Net Revenue % is acceptable & above average Sales Cost/Net Revenue % would be unacceptable.

Each expense per net revenue % would have a different average based on the previous logic.

(This is simplified, of course there are a lot more records/rows)

I would like to colour code based on previous logic on a single graph/visual.

Kind Regards;

Khalid

• ###### 1. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Khalid,

Generally (without seeing your specific workbook), comparing with an overall average is a good application for Level-of-Detail expressions.

Say, for example, you have the following calculation for a single region:

`[General & Admin Expense] / [Net Revenue]`

Then you could create an LOD expression that gives you the weighted average %.

[General & Admin LOD Weighted Avg]

`{ EXCLUDE [Region] : SUM([General & Admin Expense]) / SUM([Net Revenue]) }`

…or, if you are trying to get an unweighted average of the %'s:

[General & Admin LOD Unweighted Avg]

`{ EXCLUDE [Region] : AVG([General & Admin %]) }`

Then use that as a point of comparison for building your color coding.

Does that help? If not, we may need to see a packaged workbook to get a better idea of how exactly your data and views are structured.

• ###### 2. Re: How can I determine good or bad %s for multiple measures?(Colour code)

I do not seem understand what you did there, I will attach a workbook with dummy data

• ###### 3. Re: How can I determine good or bad %s for multiple measures?(Colour code)

So anything above region average is bad and below is good.

Dimension breakdown:

Region than Country

MENA than Under MENA comes UAE etc.

• ###### 4. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Khalid,

Before I propose a solution, there are a couple observations / questions I have about your workbook:

1. Your data source has Expense / Net Revenue pre-computed for each row. Trouble is, you have rows for multiple countries in each region, which forces you to aggregate. You aggregated with a simple SUM, but that yields a value that is not between 0% – 100%. Is that what you intended? How should that number be interpreted?
2. If the above is not what you intended, did you mean to AVG instead? If so, is it okay that the average would be unweighted? In other words, because the % of Net Revenue has already been precomputed for each country, and we don't know what the original values were, we cannot do a weighted aggregate AVG on this data.

Let me know the answer to these questions, and then I can provide an example solution. Thanks.

• ###### 5. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Hey Christian;

1. I do understand the issue in Region since each region has many countries, but suppose we are looking at it in terms of countries. So if Australia Sum is > APAC AVG than it would be bad if it is < APAC AVG  it's good (Since it is bringing down the regional average). Typically, We look at the data from a continent perspective, but will follow the same logic.

2. Currently, values are predefined. For now, the AVG could be unweighted.

Thanks!

• ###### 6. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Khalid,

I'm attaching a workbook, but it's in Tableau 10 — hopefully you can view it.

Key point:

• The data has to be pivoted first. Because "Measure Names" and "Measure Values" cannot be referenced in formulas, there is no way to build a regional average (either with LOD Expressions or with Table Calcs) that could then be referenced for color coding.

Once the data is pivoted, you have 2 options for computing regional average:

1. LOD Expressions. You can build this as a { FIXED [Region] } or as an { EXCLUDE [Country] } depending on how you need to incorporate it in your sheet. My example uses { EXCLUDE [Country] }, but the other one is more stable in scenarios where [Country] may not be in your view to begin with.
2. Table Calculations. If [Country] is part of your view (e.g. on the Details tab in the attached examples), then you can use a table calculation to find the overall average across all the countries in the region. This is preferred over LOD Expressions for large data sets, as it is more performant and requires fewer resources, but it won't work if the data in your view isn't properly aligned to the expectations of the table calc.

The attached workbook contains an example of both approaches (with reference lines to call out the averages being compared agains). You can see that they yield the same output.

I hope this helps! Unfortunately, I wasn't able to find any way to achieve the color coding without first doing that pivot, so that's the critical first step.

• ###### 7. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Hey Christian;

I would first like to thank you for your contribution.

Suppose the %s were not calculated in excel and were done on Tableau, would that make it easier to colour code?

Also the versions I have is not compatible with yours since I have not upgraded from 9.3 version yet, thus I am unable to open the workbook.

Regards;

Khalid

• ###### 8. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Khalid,

Here's a workbook in 9.3.7 (sorry, that's the earliest I can go).

Having the %'s calculated in Tableau would give you more control over exactly how the %'s are aggregated (pre-calculated in Excel only lets you do unweighted aggregations), but it doesn't really change the need to pivot the data.

• ###### 9. Re: How can I determine good or bad %s for multiple measures?(Colour code)

Christian;

The same error would appear as the previous. You are using the newer version apparently. Anyways will attempt to update Tableau today.

Thanks;

Khallid