How to make a Parliamentary chart with Tableau Desktop

A case study to go further

III. Pivot data▲

This first viz reveals the main difficulty: for Tableau, the first pie share begins at the top center; in other terms, the pie begins at noon and turns clockwise. By comparison, Excel pies also by default at top center, but they let the user customize the angle of the first sector; if Tableau was that flexible, we could just begin at 270° (at 9 o’clock, for a less geometric term) and the hemicycle would be ready. Alas, Tableau does not include such an option, and its pies always begin at noon.

Terefore, the problem we have is basically a question of sorting order: we want a left-to-right display while Tableau forces us to begin in the very center. We will have to position our MEPs in this order:

in the first quadrant (from noon to 3 o’clock), the groups at political center right, then right wing and eventually far right
in quadrants #2 and #3 (from 3 to 9 o’clock), the ‘white’ group
in the last quadrant (from 9 to midnight), groups from the far left(1) to center-left

If we were incredibly lucky, the left wing and the right wing would balance exactly and each group would fit in its quadrant. But actually, we have to expect that one group will spill out of the right quadrant over the left one, or vice-versa. So, once the sorting issue is resolved, we will have to split this central group into two parts, the first one at center-right, i.e. at the very beginning of quadrant #1, and the second part at center-left, i.e. at the very end of quadrant #4.

I now suggest you have a break in your reading, in order to think about a strategy to solve both the sorting issue and the splitting of central group; you can then read my proposal.

III-A. Setting up the left-right axis▲

To split the pie into quadrants, the key information is the position of each group along a left-right axis. However, there is no such data in our source file, as it is a question of political interpretation, with a part of subjectivity and a part of dataviz strategy. After consulting the seating plans, I would propose you this order:

‘The Left group in the European Parliament – GUE/NGL’ (Left), including most European communist parties
‘Group of the Progressive Alliance of Socialists and Democrats in the European Parliament’ (S&D)
‘Group of the Greens/European Free Alliance’ (Greens), with ecologists
‘Renew Europe Group’ (Renew), including center-right and liberal parties
‘Group of the European People's Party (Christian Democrats)’ (EPP), with conservative right parties.
‘Non-attached Members’ (NI) … this group gathers marginal parties and MEPs who have not joined any existing group, e.g. Italian populists of M5S, Greek communists, French far-right MEPs opposing other far-right parties, Catalan independentists, and so on. The majority in the EU Parliament usually ranges from S&D to EPP, and excludes of course most of the non-attached, so in dataviz terms we should display them out of the majority: either between left and far-left or between right and far-right; as the biggest chunk of this group (12 out of 47) is the Hungarian Fidesz party, expelled from EPP group, the latter option seems more conform to a sort of political average position; moreover the seating plans locates them between EPP and ECR
‘European Conservatives and Reformists Group’ (ECR), with nationalist or far-right parties around the Polish PiS
‘Identity and Democracy Group’ (ID), with other far-right parties around the Italian Lega and the French RN. It is difficult to say which of these two groups is right-most, so once again I just followed the seating plans

As most official group names are much too long to display on a chart label, I have included shorter names, inspired from the seating plans. Of course, you can opt for different short names and/or political sorting without affecting the rest of this exercise.

Let us evolve our Group formula to implement these short names:

Group

Sélectionnez

IF RIGHT([Table Name], 1) = "1" THEN "White"
ELSE
    CASE [Political Group]
        WHEN 'The Left group in the European Parliament - GUE/NGL' THEN 'Left'
        WHEN 'Group of the Progressive Alliance of Socialists and Democrats in the European Parliament' THEN 'S&D'
        WHEN 'Group of the Greens/European Free Alliance' THEN 'Greens'
        WHEN 'Renew Europe Group' THEN 'Renew'
        WHEN 'Group of the European People''s Party (Christian Democrats)' THEN 'EPP'
        WHEN 'Non-attached Members' THEN 'NI'
        WHEN 'European Conservatives and Reformists Group' THEN 'ECR'
        WHEN 'Identity and Democracy Group' THEN 'ID'
    END
END

III-A-1. Exercise: sorting groups left to right*▲

On a simple pie chart, we could have used a Manual Sort, but here we will need to do math for splitting the central group. So, you now have to create the sorting order as a new calculated field, let us call it LR Group Position. Please beware the ‘white group’, which should be excluded from this left-right sorting.

III-A-2. Answer▲

Here is a solution with CASE… WHENs, but of course a series of IF… ELSEIFs would fit as well. The key is that the white group gets a NULL sort order, as it will exclude it from aggregations we will do afterward.

LR Group Position

Sélectionnez

CASE [Group]
    WHEN 'Left' THEN 10
    WHEN 'S&D' THEN 20
    WHEN 'Greens' THEN 30
    WHEN 'Renew' THEN 40
    WHEN 'EPP' THEN 50
    WHEN 'NI' THEN 60
    WHEN 'ECR' THEN 70
    WHEN 'ID' THEN 80
    ELSE NULL /* includes white group */
END

By default, Tableau considers this new field as a measure; this means when you use it on a chart, it will try to aggregate it by sum. The result would be far from what we expect: with 177 MEPs at position 50, the EPP group would get a sort order of 8,850, at the right of ID group whose sort order would be 62 × 170 = 4,340. To instruct Tableau our LR Group Position field is not to be aggregated, just turn it into a Dimension (on Data Pane, right-click it and choose Convert to Dimension). As this sort key is an attribute of the Group dimension, it is logical to set it as a dimension also.

The distinction between dimension and measure is crucial in Tableau, as in all BI tools. Dimensions are displayed as they are on charts, while measures get aggregated, usually by sum, to the level defined by the combination of dimensions used. Let us have an example: if I display the sales amount per product and month, I use dimensions Product and Month, and I compute the Sales Amount measure as the sum of all sales amounts in each Product#160;/ Month combination.

This mechanism is used in many tools, e.g. Excel pivot tables: in the same example, I would put Product and Months in Rows and Columns zones, while the Sales Amount would be in Values, with, once again, an aggregation by sum. It is the same thing in SQL: Product and Month would go to the GROUP BY clause, while Sales Amount would have to stay in the SELECT, within a SUM function.

III-B. Splitting the central group▲

Now, we have to distribute the MEPs of the central group to balance exactly the top left quadrant (Q4) with the top right quadrant (Q1). As this means splitting a group, we have to sort the individual MEPs along a left-right axis to find out where is the exact middle. Of course, we will not survey the political positioning of each representative: we just have to balance quadrants in the pie, so we can consider MEPs as completely interchangeable.

III-B-1. Exercise: sorting MEPs left to right*▲

Create a calculated field, LR MEP position, that should abide by the left-right sorting order of groups and forces an arbitrary left-right order within MEPs of the same group.

III-B-2. Answer▲

Our data source includes an Id field, which enjoys both the necessary qualities: it is a unique identifier and it is numeric. The MIN and MAX functions tell us it ranges from 840 to 244,571. If we divide it by one million, the result will be between zero and one. So, we just have to add the group position to this Id divided by one million:

LR MEP Position

Sélectionnez

[LR Group Position] + [Id]/1e6

Once again, this figure is an attribute of the MEP and it is not to be aggregated (and certainly not by sum), so we convert it into a dimension. Among its Default Properties, set the Number Format to 6 decimal positions, so you can display it with all significant digits. Actually, the Id should also be converted into a dimension, even if this will not have any practical consequence.

What about the white group? As the LR Group Position is NULL for this group, the addition will result in a NULL also, according to the rule of NULL propagation.

Let us check this with a quick text table, sorted along the new field. The Renew group ranges from position 40.00084 (Charles Goerens) to position 40.239973 (Bergur Løkke Rasmussen).

III-B-3. Exercise: find the middle*▲

Which calculation function can we use to compute which left-right position matches noon position? In other terms, which is the left-right position that splits MEPs in exact left and right halves?

III-B-4. Answer▲

Splitting a population into two equal halves, that is the very definition of the MEDIAN function. Moreover, as all aggregate functions, the median ignores NULLs, so our filler data will not be taken into account. If ever we had assigned them an arbitrary value like 0 or 999, the median would have been completely unbalanced and would have just separated MEPs from fillers!

If you display this median measure on a new sheet, you will learn the median MEP is at position 40.239973 (which matches the said Rasmussen, but as the order of MEPs within a group is purely random, this tells nothing about this specific MEP).

To get this simple viz, you will have to display the median as a Discrete measure instead of a Continuous one (in math jargon, discrete means discontinuous, from the latin discretus, separated). Most of the times, dimensions are discrete and measures are continuous, so these notions get easily confused, but actually you can perfectly have a continuous dimension (Time dimensions are usually continuous) or a discrete measure (like our median above).

If ever you are just discovering theses concepts, here are the instructions to get the viz. On a new sheet, drag the LR MEP Position to Rows. The pill you get is a blue dimension, i.e. displayed in a discrete way. Right-click this pill to turn it into a Measure, with Median as the aggregate function; the pill becomes green, as by default Tableau displays measures as continuous (in this case, a vertical bar). We do not want a chart but just a figure, so you right-click the pill again and you set it as Discrete.

The MEPs to put in the top left quadrant are those whose left-right position is lower than the median, while the ones to put in the top right quadrant are those whose left-right position is greater than the median. To test whether it will be so easy, filter out the fillers and display the full names and the LR MEP Positions on the test sheet with the median. As you can see, the median is now changing its value on each row, and matches the MEP’s individual position.

This is annoying but perfectly logical: the median is now computed on only one MEP, so he or she is necessarily the median MEP in the population with only himself or herself.

III-B-5. Exercise: compute noon position***▲

This way of calculating the median is perfectly logical, but very annoying for us. We have to compare each MEP to the median, so we know whether the MEP should be on the left quadrant or the right quadrant. Hence, we have to be able to compare the individual position of each MEP to the global median, i.e. computed on the whole population of MEPs.

We could of course create a constant or a parameter and value it to 40.239973. That would be good enough for a single-use viz, but this figure would become wrong as soon as the source data is modified or updated. For an elegant and enduring solution, you now have to create a new calculated field, Noon Position, to bring to global median down to detailed level ((MEP).

III-B-6. Answer▲

The point is to compute the median at level of detail different from the one where it will be used: this is the role of a specific Tableau syntax, the LOD expressions.

Noon Position

Sélectionnez

{FIXED: MEDIAN([LR MEP Position])}

The curly brackets indicates it is an LOD calculation. The calculation at the right of the colon (i.e. the median) is computed in the context specified at the left of the colon. In our case, the FIXED keyword used alone means the global context, i.e. the whole dataset.

For the global context, Tableau accepts a shorthand syntax, without the FIXED keyword nor the colon. So, you could write simply: {MEDIAN([LR MEP Position])}

If you do not know the LOD expressions, I invite you to some reading, starting with the official documentation. Mastering this syntax and the underlying logic will make you an advanced Tableau developer!

The LODs are the equivalent of Analytic Functions in SQL (also called Window Functions as the calculations is performed through a window on data different from the current data row). In SQL code, the Noon Position would have been: MEDIAN(LR_MEP_Position) OVER ()

This Noon Position is a single value, that should not get aggregated, so it is fit to make it a dimension. On our test table, replace the wrong median by this dimension, and you should get the right value on each row:

III-B-7. Exercise: compute quadrants*▲

Now we have the Noon Position, you just have to create a Quadrant calculated field to compute the quadrant of each MEP or filler. Here is the detailed specification, according to the sketch presented above:

MEPs at the right of Noon Position go to quadrant #1
fillers go to quadrants #2 and #3 (no need to differenciate between them)
MEPs at the left of Noon Position go to quadrant #4

III-B-8. Answer▲

There are several ways of recognizing fillers; to be consistent, I have written the formula so that all quadrants rely on the same LR MEP Position field, but you could have used Group, Table Name or LR Group Position. Eventually, the median MEP (Rasmussen) could be positioned in either quadrant #1 (I did this by using >= and then <, to avoid the exceptional circumstance of having groups fitting exactly in their quarters) or quadrant #4 (if you use > and then <=), but you should not forget him (if ever you used > and <), lest you unbalance the quadrants.

Quadrant

Sélectionnez

IF [LR MEP Position] >= [Noon Position] THEN 1
ELSEIF ISNULL([LR MEP Position]) THEN 2
ELSEIF [LR MEP Position] < [Noon Position] THEN 4
END

Once again, this quadrant number is not to be aggregated and should be a dimension (I promise I will give a different answer to this question by the end of this paper).

III-B-9. Exercise: compute sectors*▲

Each political group will get its pie share, except the central group which will get two, one at center-right and the second at center-left. Just create a Sector calculated field to implement this notion.

III-B-10. Answer▲

You just have to combine Group and Quadrant, e.g. with a formula like this one:

Sector

Sélectionnez

[Group] + STR([Quadrant])

The mistake (or rather the useless complication) would be to make an exception for the central group. If Group and Quadrant were unrelated the formula above would produce two sectors per group, e.g. S&D1 and S&D4, but actually Group and Quadrant are both determined by the left-right positions. As all S&D MEPs are left of the Noon Position, the S&D4 sector will be the only one brought into existence. Conversely on the right wing, EPP1 will exist but not EPP4. The only group with two sectors will be the one split by the median, i.e. Renew group with sectors Renew1 and Renew4.

As the Sector field is computed from two dimensions, Tableau spontaneously qualifies it as a dimension, which is exactly what we need.

III-C. Sorting sectors in clockwise order▲

Let us be back to our first draft pie.

III-C-1. Exercise: change granularity of the chart**▲

The color is determined by the group, which is what we want. Yet, we want two same-colored sectors for the central group, how to do it?

III-C-2. Answer▲

The Detail tool on the Marks shelf is precisely the one to enforce a finer grain without displaying explicitly the corresponding dimension. You just have to drag the new Sector field on this tool to split the central group into two sectors.

The 101 MEPs in the Renew group are now separated into two sectors, Renew4 with 100 MEPs and Renew1 with just 1.

III-C-3. Exercise: sort clockwise**▲

Now you just have to sort the sectors in the right order, i.e. from noon to midnight; according to our four-quadrant sketch, sectors must begin with center-right, go to far right, go on with the fillers, resume with MEPs at far left and finish at center-left. Here the instructions for this exercice:

create a calculated field, Clockwise Order, to implement this order
choose the right qualification (dimension or measure) for this calculated field and set the necessary default properties
use this calculated field so that sectors get ordered as we want

III-C-4. Answer▲

The formula should sort by quadrant number first, and then in left-right order within each quadrant. There are several ways to do it, this is my proposal:

Clockwise Order

Sélectionnez

[Quadrant] * 1000 + ZN([LR Group Position])

Hence, Renew1 (the right part of Renew) will get the first position with 1,040, then EPP1 (1,050), NI1 (1,060), ECR1 (1,070) and ID1 (1,080). As the LR Group Position is NULL for fillers, we need the ZN function to transform this NULL into zero, so they get position 2,000 (without ZN, the rule of NULLs propagation would give them a NULL position, and they would be the first sector). Then we will get Left4 at position 4,010, and so on up to Renew4 on 4,040.

Let us now discuss the qualification: the Clockwise Order is an attribute of Sector, there is no reason to aggregate it, so making it a dimension would sound logical. However (I had warned you I would change my mind), we will use this field as a sort key, and Tableau always sorts on aggregates, even when the sort key is a dimension. By default, the aggregate function would be sum, with the regrettable effect of multiplying our finely calibrated result by the number of MEPs in each sector!

As we have to endure an aggregation, we would better choose it explicitly. Hence, I advise you to let the Clockwise Order as a measure, so you can set its Default Properties > Aggregation to a function that keeps the original value. As all the MEPs in the same sector will get the same Clockwise Order value, you can choose either average, median, minimum or maximum.

We want to sort sectors, so let us right-click the Sector pill on Detail and set a Sort by Field according to our Clockwise Order. You will be pleased to notice our default aggregation comes up automatically.

At this point, you may feel a certain disappointment, as nothing happens. Actually, Tableau gives the sorting priority to the first pill in the shelf, in this case the Group. As our Sector is the last pill, it cannot have any say in the sorting!

Just modify the order of pills by dragging the Sector and dropping it above the Group. Shazam, the sectors now position exactly like we decided:

III-D. Finishing touches▲

Our base structure is now sane, but the hemicycle is not yet ready for end users.

III-D-1. Exercise: choose colors**▲

Colors are always a hazardous matter in dataviz, with developers navigating through business habits, technical requirements, cultural connotations and his/her own subjectivity. Of course, it is even more sensitive with a political topic (OK, that is quite obvious for the Greens, but what about ID?).

You now have to find an indisputable color for each political group.

III-D-2. Answer▲

If you have visited the online seating plans to get inspiration from official colors, you deserve a star. The second star is for them who have used the Pick Screen Color tool, or who scrapped the hexadecimal codes from HTML.

Dark red (#460000) for The Left
Red (#C80000) for S&D
Green (#32C800) for The Greens
Orange (#FFAA00) for Renew
Dark blue (#003C78) for EPP
Gray (#666666) for NI
Light blue (#0082FF) for ECR
Purple (#9B20A9) for ID
last but not least, the white group in white (#FFFFFF)!

You can use hexadecimal color codes directly in Tableau:

III-D-3. Exercise: cleaning up*▲

Our pie is starting to look like a hemicyle!

We did add a good half of filler data, but the user should ignore this artifact. Erase the traces!

III-D-4. Answer▲

The first thing to do is to suppress the legend of CNT(MEPs), which reveals there are twice as many MEPs as it should. You can Hide the card, use Analysis > Legends, or more simply remove the pill from the Size tool.

We have keep the color legend, but it should not display the white group. You could rename it to "" or NULL in the Group formula, but that would force you to review the depending formulas. It is more simple and more efficient to use an alias (right click the Group field, then Aliases). Tableau rejects an empty alias, but you can use a white space.

The color legend is displayed in alphabetical order. It would be more readable in left-right order; to get this, sort colors on a neutral aggregation of LR Group Position (average, median, minimum or maximum).

Eventually, you can use Worksheet > Tooltip to avoid tooltips with too revealing information, more particularly when hovering the white group.

III-D-5. Finishing the first version▲

Just add a meaningful title, and we have a consistent hemicycle.

(1)

Let me precise I am using the term ‘far’ to specify the geometric position of MEP groups in the hemicycle. This paper does not mean any judgment about the political orientation of groups located at the extremities, but just to propose technical mechanisms to represent any parties in any assembly.

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2023 Antoine Dinimant. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.