Shulou(Shulou.com)06/03 Report--

Tableau is a software that is very easy to learn and use. After two or three hours of introductory tutorials, you can make a report that looks good and has a great sense of achievement. However, when you learn to a certain extent or engage in data visualization for a period of time, you will find that your Tableau level has encountered a bottleneck, which is difficult to break through. Sometimes a small problem at work can make you worry all day without thinking about food and tea. Indeed, Tableau software is full of a variety of skills, which can only be mastered through a large number of practical work. With the accumulation and continuous summary of experience, the leap of Tableau level from quantitative change to qualitative change can take place. What I want to say here is that working in Tableau requires a strong ability to think of others. Sometimes conventional methods can not achieve the goal, might as well change the thinking, from another point of view, the use of other techniques, or even "deceptive" means to achieve the same effect.

We know that a percentage of a metric calculated by a table under the action of a dimension filter cannot maintain the original value. For example, if you choose any number of Subcategory values, the sum of them must be 100 percent, and it is clear that their respective percentages are completely different from what they were when there was no metric filter.

So how do you keep each percentage at its original value with the help of a dimension filter? Here are five methods.

Method 1: use the Index function

Create a calculated field Index:Index (), convert it to discrete, and then select a different number in the control filter window to display the percentage value of the corresponding Subcategory before the filter. Here, Index (), as a table calculation filter, has the lowest level, and it can only choose which value the corresponding dimension presents, but it has no effect on the table calculation itself, so the percentage value is maintained.

What is used here is a way of thinking about others. Since the filter level is too high to affect the table calculation result, I use the lowest level filter (the table calculation filter) to convert the dimension value represented by the string into a number that can be expressed in Index to achieve the same effect. Of course, this approach has two major disadvantages. First of all, it is not clear which Subcategory corresponds to 1-17 in the Index option. Second, when the Index is arranged according to other standards instead of the alphabetical order of the Subcategory, for example, according to the size of the Sales, the corresponding Subcategory will change, so although the idea is good, this method is not a good choice.

Method 2: use set-valued (Set)

Based on Subcategory, a set (Set) arranged according to Sales size is built, the parameter Top N is set up to realize dynamic selection, and Set is put into the color tag.

Hide the Out in the color legend and select different parameters to display the original percentage of the corresponding Subcategory.

No filter is used here, but the Subcategory and its percentage value of the Out category are hidden to visually show the Subcategory and its original percentage value of interest, which is a clever way to achieve the same effect through "deception". The disadvantage of this method is that because of the characteristics of Set, it is only suitable for selecting dimensions and percentages arranged according to a certain characteristic, especially the size of measures, and cannot freely and randomly select dimensions and present percentage values.

Method 3: copy the data source

We know that the Percent of Total is calculated as: SUM ([Sales]) / TOTAL (SUM ([Sales]))

If the data source is copied, the dimension filter and the Sales in the numerator use the values in the same data source, while the Sales in the denominator uses the value in another data source. As long as you break away from the connection between the two data sources, the dimension filter can only affect the Sales in the numerator, but there is nothing you can do about the Sales in the denominator, so you can maintain the percentage before the filter. This is how this method works.

% in Double Data Sources:

SUM ([Sales]) / TOTAL (SUM ([Orders (Sample-Superstore) (2)] .[ sales]))

The disadvantage of this method is that it requires two data sources of Blending, which increases the file size and affects the speed and performance of report rendering.

Method 4: use the lookup function

The percentage value calculated by the table calculation cannot be maintained because the dimension filter is ranked above the table calculation in the filter and calculation order table, so the table calculation is controlled by the dimension filter. If we turn the dimension filter into a table calculation filter, the original percentage value can be maintained. The Index function is used in the first method, where we can use the lookup function, which is also a commonly used table calculation function. Establish the following calculated fields:

Lookup:

LOOKUP (MIN ([Sub-Category]), 0)

The purpose of the MIN function here is to turn Subcategory into an aggregate calculation, which is required by lookup, but you can also use MAX, ATTR, and so on.

This method is an improvement over the first method of using Index, because the specific value of the dimension is displayed in the filter option, just like using Subcategory as a filter directly. The disadvantage is also obvious that when another dimension, such as Region,Category, is used as a filter, its lookup calculated field must change the selected dimension, which is very troublesome.

Method 5: calculate with table range LOD

Here comes the killer! This method calculates the percentage using the level of detail calculation of the table range.

% by LOD:

SUM ([Sales]) / SUM ({SUM ([Sales])})

In this calculation, the table range included by the denominator LOD calculates the total sales, which belongs to the Fixed level of detail calculation and is not affected by the dimension filter, so all selected dimensions will maintain their original percentage values.

Methods 1 and 4 use the idea of lowering the filter, and here the dimension filter is not changed, but the Fixed LOD is used to increase the calculation level and go beyond the dimension filter to achieve the goal. There are all roads leading to Rome!

Why is this method so good? Please note that this calculation uses measurements and does not involve dimensions, so no matter which dimension you choose as a filter, you do not need to modify the formula and always work perfectly, which is better than method 4. In addition, the selection of dimension values can be random and do not have to be arranged according to metrics or other criteria, which is an advantage over method 2. It does not need to require dual data sources like method 3. Overall, method five is the best choice, kill all other methods in seconds!

Thinking of others is ubiquitous in Tableau. Basically, the techniques and tricks in Tableau that make you laugh at others are all about being considerate of others, which is, to put it bluntly, "cheating". In the future series, I will continue to teach you to use "deception" techniques to overcome difficulties one by one, gods and ghosts do not know how to reach the goal!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.