How to make a Parliamentary chart with Tableau Desktop

The easier way is to directly position the calculation at group-level with a FIXED:

{FIXED [Group] : COUNT([MEPs])}

This figure should be displayed as it is and not aggregated, so you should make it a dimension.

As the labels are displayed in a context with two dimensions, Group and Sector, another way is to exclude the Sector dimension to position the calculation in a context with only Group dimension:

{EXCLUDE [Sector] : COUNT([MEPs])}

Tableau refuses to make a dimension with this version, so you will have to settle for a discrete measure.

We now have got the right figure for the central group, we just have to hide the label of the minority sector. The syntax will quickly become intricate, so let us do it step by step.

First, you have to climb up to the group level, and there get the MAX of the lower level headcounts, i.e. of sector headcounts. To tell it the other way round, we COUNT at sector-level and then we MAX these figures at group-level. Technically speaking, you have to nest one LOD into another:

{EXCLUDE [Sector] : COUNT([MEPs])}

The maximum headcount should now display on both sectors:

The sector headcount is just COUNT([MEPs]), the headcount of the bigger sector in group is the nested formula we just wrote, so the natural way of coding this would be:

/* comparison test (mixed levels error) */
IF COUNT([MEPs]) = {FIXED [Group]: MAX({FIXED [Sector] : COUNT([MEPs])})}
THEN "label"
ELSE "nothing"
END

However, Tableau considers this formula incorrect, arguing that it ‘cannot mix aggregate and non-aggregate arguments with this function’.

Actually, the result of an LOD formula is syntactically considered as a detail-level value. Hence, our {FIXED…} has the correct value of 100, but this 100 is at MEP-level. To compare it with an aggregate value like the sector headcount, we have to aggregate it. You can use whichever aggregate function among AVG, MEDIAN, MIN or MAX, even SUM will do (we fixed on GROUP and there is only one group for the sector). My preference is for ATTR, because in unexpected situations it will kindly raise an error rather than send a wrong result.

Here is the correct formula (ATTR version):

/* comparison test (mixed levels error) */
IF COUNT([MEPs]) = ATTR({FIXED [Group]: MAX({FIXED [Sector] : COUNT([MEPs])})})
THEN "label"
ELSE "nothing"
END

Tableau considers this formula as too complex for a dimension, so if it was one Tableau automatically converts it to a discrete measure. Hence the pill may become red as this measure can no longer be used as a dimension on the viz.

If ever you encounter this issue, just drag again the LOD label field on the red pill, so Tableau now displays it as an AGGregate calculation.

With this fix, all the labels should now show, except the one for the right part of Renew:

You just have to assemble the pieces, and pay attention not to mix aggregates and details, nor texts and numbers:

/* if white group, no label,
   else, if bigger sector (or lone sector), group name and headcount,
   else (if minority sector), no label */
IF ATTR([Group]) = "white"
  THEN NULL
ELSEIF COUNT([MEPs]) = ATTR({FIXED [Group]: MAX({FIXED [Sector] : COUNT([MEPs])})})
  THEN ATTR([Group]) + ": " + STR(ATTR({FIXED [Group] : COUNT([MEPs])}))
ELSE
  NULL
END

All this fuss just for hiding two labels? Yes. With Tableau, the finishing details are often the harder to get. But if you have followed it up to this, you now are an accomplished LODer!

The Table Calc should be performed along both dimensions, Group and Sector, at the deepest level (sector), it should not restart, and it has to follow the Clockwise Order.

A simpler way of getting the right order is to swap dimensions so that Sector gets priority:

Either of these settings should get you the expected result:

You have now determined the right options for the Table Calculation. Let us edit the calculated field in the Data Pane and set its Default Table Calculation accordingly.

=>

You can use the formula below, or many other variants. If the result does not change, or if the pill becomes red, re-drag the calculated field to replace the erroneous pill.

"First: " + STR(FIRST()) + CHAR(10) + "Last: " + STR(LAST())

I have used STR and concatenations to get both values at once, but of course you could display them alternatively.

No subtlety here, just adapt the syntax example from the Tableau doc:

LOOKUP(COUNT([MEPs]), LAST())

Here is an example solution, but of course there are many ways to write this formula.

// in all cases, group name and colon
ATTR([Group]) + ": " 
+ STR(
  // case 1, white group => NULL
  IF ATTR([Group]) = 'White' 
    THEN NULL
  // case 2, regular sector => headcount
  ELSEIF FIRST() != 0 AND LAST() != 0
    THEN COUNT([MEPs])
  // case 3, bigger sector among first and last => first headcount + last headcount
  ELSEIF FIRST() = 0 AND COUNT([MEPs]) >= LOOKUP(COUNT([MEPs]), LAST())
      OR LAST() = 0  AND COUNT([MEPs]) > LOOKUP(COUNT([MEPs]), FIRST())
    THEN LOOKUP(COUNT([MEPs]), FIRST()) + LOOKUP(COUNT([MEPs]), LAST())
  // case 4, smaller sector among first and last => NULL
  ELSE
    NULL
  END
)

The big IF… END computes the headcount, then it is converted to string and concatenated to group name and colon; the rule of NULLs propagation will provide us a NULL label for the white group and the minority sector. Last, if ever the central group gets split in two exactly equal parts, the label will arbitrarily show on the first sector (i.e. on the right side), as the conditions are ‘first >= last OR last > first’.

No need for an ‘Arc’ formula, just right-click the Group field and choose Create > Group to get arcs (yes, arc are groups of Groups, sorry for the homonymy).

For sub-arcs, you can either group sectors or create a formula to concatenate the quadrant number to the arc.

[Arc] + STR([Quadrant])

We will use Dual Axis, so we need a measure; both our pies must be identical and positioned at the same height, so the measure has to bear exactly the same value on each share of both pies. It has to be constant on detailed data, and it has to remain constant when it gets aggregated.

In the screenshots below, I am using a calculated field named Constant, whose formula is ATTR(1), which will result in 1 in all cases. You can use as well MIN(1), which is customary on Tableau forums, MAX(1), MEDIAN(1), MIN(123456), SUM(0) or even a simple zero (aggregated by sum, it will still be zero).

Create such a Constant, then drag it to the Columns shelf; that makes the pie smaller, but we will fix that later. Now catch the Constant pill you have just dropped on Columns, drag it a little farther to the right, and keep pressed the Control key on your keyboard.

You now have your two pies:

Please notice you now have three sections on the Marks shelf, so you can either edit both pies together, or just the first one, or just the second one.

First, click on the third section of Marks shelf, the one for pie #2. Here is the to-do list:

• on Color, drag Arc instead of Group
• on Detail, drag Sub-arc instead of Sector
• sort sub-arcs according to ascending average of Clockwise Order; if needed, make it the first pill
• edit the colors to get something politically meaningful, and choose a low Opacity (40% on the screenshot below)
• duplicate the LOD Label field (or the TC Label), then rename it to Arc Label
• in the formula of Arc Label, replace all occurrences of [Group] by [Arc], 'White' by ' ', and all occurrences of [Sector] by [Sub-arc]
• use this Arc Label on Label instead of LOD Label or TC Label
• hide the horizontal axis (right-click it and uncheck Show Header)
• if needed, hide the legend of AGG([Constant]) and the color legend

You should get something like this:

To get Dual Axis, just right-click the second Constant pill. Getting the sizes correct is a bit more complicated: as long as there is a COUNT([MEPs]) pill on the Size tool of both pies, they will always get the same size. Remove it from both pies so you can set sizes manually.

Then, you will probably notice that the colors of the groups pie are darkened: this is due to the arcs pie overlapping the groups pie. Just swap both Constant pills to reverse the overlapping order, and you get your original colors back.

A first obvious solution is to divide by the total headcount nonetheless, and then multiply by two:

// LODs version (ATTR to avoid aggregation)
ATTR({FIXED [Arc]: COUNT([MEPs])} * 2 / {COUNT([MEPs])})

// Table Calc version
COUNT([MEPs]) * 2 / TOTAL(COUNT([MEPs]))

This is correct as long as the fillers are an exact half of the total headcount. However, on next chapter, we will exclude some fillers, so this formula will become wrong. Hence my demand for an alternative solution.

So the total headcount (denominator) should include only real MEPs and ignore fillers. For this, we just have to count a field where fillers are NULL, e.g. LR Group Position. As aggregate functions ignore NULLs, only real MEPs will be counted.

// With LODs
ATTR({FIXED [Arc]: COUNT([MEPs])} / {COUNT([LR Group Position])})

// With Table Calc
COUNT([MEPs]) / TOTAL(COUNT([LR Group Position]))

Keep this second version rather than the multiplication by two, format it as percentage with one decimal position, and test your percentages on a new sheet. You should get these results:

• Left wing: 35.7%
• Right wing: 39.4%
• Others: 6.7%
• Far right: 18.2%

The easier is to keep two different calculated fields, one for the arc name and the other for the percentage, so you can set a default number format to the percentage.

// With LODs
IF ATTR([Arc]) = ' '
  THEN NULL
ELSEIF COUNT([MEPs]) = ATTR({FIXED [Group]: MAX({FIXED [Sub-arc] : COUNT([MEPs])})})
  THEN ATTR([Arc])
ELSE
  NULL
END

// With Table Calcs
IF ATTR([Arc]) = ' ' 
  THEN NULL
ELSEIF FIRST() != 0 AND LAST() != 0
    OR FIRST() = 0  AND COUNT([MEPs]) >= LOOKUP(COUNT([MEPs]), LAST())
    OR LAST()  = 0  AND COUNT([MEPs]) > LOOKUP(COUNT([MEPs]), FIRST())
  THEN ATTR([Arc])
ELSE
  NULL
END

// With LODs
IF ATTR([Arc]) = ' '
  THEN NULL
ELSEIF COUNT([MEPs]) = ATTR({FIXED [Group]: MAX({FIXED [Sub-arc] : COUNT([MEPs])})})
  THEN ATTR({FIXED [Arc]: COUNT([MEPs])} / {COUNT([LR Group Position])})
ELSE
  NULL
END

// With Table Calcs
IF ATTR([Arc]) = ' ' 
  THEN NULL
ELSEIF FIRST() != 0 AND LAST() != 0
  THEN COUNT([MEPs]) / TOTAL(COUNT([LR Group Position]))
ELSEIF FIRST() = 0  AND COUNT([MEPs]) >= LOOKUP(COUNT([MEPs]), LAST())
    OR LAST()  = 0  AND COUNT([MEPs]) > LOOKUP(COUNT([MEPs]), FIRST())
  THEN (LOOKUP(COUNT([MEPs]), FIRST()) + LOOKUP(COUNT([MEPs]), LAST())) / TOTAL(COUNT([LR Group Position]))
ELSE
  NULL
END

If ever you want to stick to the one-formula solution, you will have to format the percentage yourself with a good deal of concatenations:

// With LODs
IF ATTR([Arc]) = " " 
  THEN NULL
ELSEIF COUNT([MEPs]) = ATTR({FIXED [Group]: MAX({FIXED [Sub-arc] : COUNT([MEPs])})})
  THEN ATTR([Arc]) + ": " + STR(
    ROUND(
      100 * ATTR({FIXED [Arc] : COUNT([MEPs])} / {COUNT([LR Group Position])}),
      1)
  ) + "%"
ELSE 
  NULL
END

// With Table Calcs
ATTR([Arc]) + ": " + STR(
  IF ATTR([Arc]) = ' ' 
    THEN NULL
  ELSEIF FIRST() != 0 AND LAST() != 0 
     THEN 
      ROUND(
        100 * COUNT([MEPs]) / TOTAL(COUNT([LR Group Position])),
        1)
  ELSEIF FIRST() = 0  AND COUNT([MEPs]) >= LOOKUP(COUNT([MEPs]), LAST())
      OR LAST()  = 0  AND COUNT([MEPs]) > LOOKUP(COUNT([MEPs]), FIRST())
    THEN ROUND(
        100 * (LOOKUP(COUNT([MEPs]), FIRST()) + LOOKUP(COUNT([MEPs]), LAST())) / TOTAL(COUNT([LR Group Position])),
        1)
  ELSE
    NULL
  END
) + "%"

OK, you now have all the data necessary to run for president of the European Parliament!

You of course have to create a Parameter, e.g. ‘Overflow %’, with a Range from 0 to 1 (i.e. 0% to 100%), then Show it.

As we have set a validity range and a step, the parameter is displayed as a slider:

I propose you to use the PERCENTILE function: to remove 20% of the fillers, you have to sort them on the identifier, then compute the identifier value under which there are 20% of the headcount… this is the very definition of the 20^th percentile.

[Group] <> 'White' OR [Id] >= {PERCENTILE([Id], [Overflow %])}

The percentile has to be computed on the whole dataset, like the median before, hence the shorthand LOD {}. Now, you just have to put this field on Filter, keep only True data, and you can test the slider:

Everything works, however there is no proportion between the Overflow % and the angle you get. With an overflow of 50%, the user would probably expect a right angle, i.e. an arc of 270°. As you can see on the screenshot above, the arc is rather two thirds of a circle, i.e. 240°.

Is there a bug? With 705 MEPs and an overflow of 50%, you get 353 fillers (my flag above uses the >= operator). Hence, MEPs are 705 / (705 + 353), i.e. 66.6% of a circle. Our 240° stands the test of math! However, our system is counter-intuitive, and that’s never a good news in dataviz. In a perfect system, the user would choose his/her angle, then we would compute the matching percentage of fillers to remove; however, the PERCENTILE function does not support a calculated percentile number, but only a constant or a parameter. As long as we use the percentile method, we are stuck with our Overflow %. It does the job, but is hardly understandable for users.

For a better user experience, we will perform a cognitive trick: keep the same system, but hide the percentage and display the angle instead.

First, let us formulate an equation, just on paper. Here are the variables:

• x: our target angle, in degrees
• n: the count of real MEPs
• p: the percent of overflow chosen by user

With an overflow of 10%, we should display 100% of real MEPs and 90% of the fillers; so, in math, we have n(1 – p) fillers. The pie share of real MEPs will be n / (n + n(1 – p)). This ratio is also equal to the pie share occupied by our x angle, hence the equation:

      x / 360 = n / (n + n(1 – p))
?   x / 360 = 1 / (2 – p)
?   x = 360 / (2 – p)

Let us translate this into a calculated field:

360 / (2 - [Overflow %])

This result should not get aggregated, you can make it a dimension. It is expressed in degrees, without any decimal position, so a good number format would be 0°.

Hiding the percentage is easy, just Customize the parameter card to uncheck Show readout. Then, Edit the card title so there is no trace of any %.

Let us display the angle obtained: just right-click it and choose Show Filter, then Customize the new card and uncheck Show "All" Value. Last, the checkbox is a risk: if a user unchecks it, the whole chart disappears. You cannot remove it, but you can turn it into a dropdown list or a single-value list (radio button) that are much safer.

Eventually, move the filter card below the parameter card, so users understand the angle results from the slider.

Dataviz is a matter of communication: you have to both deliver the relevant information, the angle, and prevent it from getting polluted by a less relevant information like the percentage. Users can now pilot the angle with the slider, without bothering with the intermediate calculation.

No specific difficulty here:

Just re-use the same equation than before, and reverse it to express the percentage (p) as a function of the angle (x):

      x = 360 / (2 – p)    2 – p = 360 / x    p = 2 – 360 / x

Let us test this: if the user wants an arc of 240°, we will have to remove 2 – 360 / 240 = 0.5, i.e. 50% of fillers. This does match our previous experiments.

Here is the Tableau formula:

2 - 360 / [Angle Desired]

As usually, the result is a constant to convert into a dimension so we avoid aggregation.

To remove e.g. 20% of fillers, you just have to filter on RANDOM value greater than 0.2. As the generated random number is between 0 and 1, it will be true in approximately 80% of the cases and false in approximately 20%.

[Group] != 'White' OR RANDOM() > [Exclude %]

As you have probably noticed, first RANDOM does not show in the list of Tableau functions, and second you have to type it in entirely, without the help of word completion. Well, at least it is recognized as correct by the syntax validation. As usually, this calculated field should not get aggregated, so you can make it a dimension.

You just have to Show the Angle Desired parameter, then drag Monte Carlo Filter on the Filter shelf, and choose True.

And that’s all, folks! Here is an example of the final result:

Drawing randomly implies some approximation. You can notice, on your own workbook or the screenshot above, that shares from both pies do not match exactly. As our median enforces symmetry, maybe it can pass for a stylistic effect?