It was Luke’s turn to provide the challenge for this week – to produce a modified mekko chart (or marimekko chart / mosaic plot – see here for more information).

Luke suggested it would require LODs to solve, but that it would also be possible with table calcs. I tackled it with a mixture of both, mainly based on what felt right at the time.

Like most challenges I worked out the data I needed for the final viz in tabular form first, so I could ratify the numbers and calculations I built before building the viz.

- Building out the data
- Building the viz

Building out the data

The y-axis of the viz plots the % of profitable orders for each **Sub-Category.** So the first thing we need to do is identify a profitable order. A single order can have multiple product lines, where each product line might be associated to a different **Sub-Category. **We need to determine whether the profit against all the products for the same **Sub-Category** on a single order is positive (ie profitable) or not. We use an LoD for this

**Order Is Profitable**

IIF({FIXED [Order ID], [Sub-Category]: SUM([Profit])}>0,1,0)

For each **Sub-Category** within a single **Order ID**, check if the total profit is a positive number. If it is then return 1 else 0. I’m purposefully choosing 1 and 0 to help with the next step.

To determine the % of profitable orders, we need to know the total of all the profitable orders as a proportion of all the orders for a **Sub-Category.**

**Count Orders**

COUNTD([Order ID])

The number of distinct orders.

**Profitable Orders**

SUM([Order Is Profitable])/[Count Orders]

formatted to be a percentage with 0 dp.

Put these into a table, along with the **Sales** measure, and you can see what’s going on

We need to sort this data, first by **Profitable Orders** desc, then by **Sales** desc. And we need to sort in a way that can be used once this table of data is displayed in the required Viz format. If we just wanted to sort this table of data displayed here, we can use a technique described in Tableau’s KB here. However this doesn’t work for the viz, as it relies on the newly created dimension existing on the **Rows** shelf, which won’t work when we get to building the viz, as we can’t put it on rows. Since the field also contains table calculations (Rank), you can’t reference the field in the Sort option of a pill.

Anyway, based on this, and after a bit of trial and error, I managed to create a sort field that I could reference.

**Sort**

STR(ROUND([Profitable Orders],2)) + “-” + STR(ROUND(SUM([Sales])/1000000,2))

Here I’m building up a string field combining the **Profitable Orders** field, that I’ve rounded to 2 decimal places, with the **Sales** field that I’ve rounded to $millions at 2 decimal places. This is to ensure that since we’re working with string data, 800 is ordered after 8000 when sorting descending.

Pop this into the view, and set the sort property of the **Sub-Category** pill to sort by **Sort descending**

There may be a better way of doing this, and I’m not sure it would work in all circumstances, but it worked for this challenge. I’ll be interested in seeing how others approach this element of the challenge.

Now, that’s resolved, let’s get the other fields we need.

Along with the **Profitable Orders %**, we also need to display the percentage of non-profitable orders

**Non-Profitable Orders**

1 – [Profitable Orders]

I also chose to format this to % with 0dp (purely for display purposes in the table).

The width of the bars in the viz, is based on the % of total sales, so let’s work that out…

**% of Total Sales**

SUM([Sales])/TOTAL(SUM([Sales]))

TOTAL is a table calculation that ‘totals’ up the sales in the table. In this case we care about the whole table, but as with any table calculation it can be set to apply to certain partitions in the view.

Having worked out the % of Total Sales, we now need to work out where to plot each **Sub-Category** along the x axis, as due to the variable width of the bars, we can’t just use the **Sub-Category** dimension on the **Columns** shelf.

Each **Sub-Category** is going to be positioned based on its** % of Total Sales **relative to it’s position in the sort order. That is we need to work out the cumulative value of **% of Total Sales**.

**% of Total Sales Cumulative**

RUNNING_SUM([% of Total Sales])

This totals up the **% of Total Sales** as we go down the rows in the table, as shown below. I’ve included column grand totals, so you see that **% of Total Sales** adds up to 100%, and the cumulative version sums up the previous values as it goes down the table, ending in 100% (note the numbers have been rounded due to 0dp, but if you changed the formatting, you’d see this better).

Now we have all the core elements we need to build the viz.

Building the Viz

On a new sheet,

- add
**Profitable Orders**to**Rows** - add
**Sub-Category**to**Detail** - add
**% Total Sales Cumulative**to**Columns**

Edit the table calculation setting of the **% Total Sales Cumulative** field, so that it is computing by **Sub-Category** (Compute Using -> Sub-Category option from the drop down /context menu of the pill).

Set the sort option against the **Sub-Category** pill to sort by the **Sort** field descending

Change the mark type to **Bar**.

Add **% of Total Sales** to the **Size** shelf. Then change the **Size **option (by clicking on the button) from Manual to Fixed, and set the alignment to Right

Add **Non-Profitable Orders** to **Rows,** then right-click on the relevant axis, Edit Axis, and set to reversed

This is the basic viz – it just now needs formatting

- Add Measure Names to
**Colour**and adjust accordingly - Set a white border around each bar (via the
**Colour**shelf) - Edit the
**% Total Sales Cumulative**axis, and change to start from -0.05 which will give a bit of space at the front - Remove all row / column borders and gridlines
- To get the black bar visible across the 0 line, I ended up adding a reference line; a constant set to 0, formatted to a black line.
- Hide the axes
- Add appropriate fields to the
**Tooltip**and set accordingly. - Add
**Segment**to the**Filter**shelf.

Then just add to a dashboard, and you’re all set. My published viz is here.

Happy vizzin’! Stay Safe!

*Donna*