Tag: MIN(1)

Customer Purchasing Habits (RFM Analysis)

Donna ColesLeave a comment

Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.

The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.

Defining all the calculations for the main viz

First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this

pToday

Date constant set to 01 Jan 2022

We also need to understand the latest order date per customer, so need

Max Order Date By Customer

DATE({FIXED [Customer ID]: MAX([Order Date])})

and so with this we can calculate the days since last order, which is one of our key measures.

Days Since Last Order

DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])

The other measures we need are

#Orders

COUNTD([Order ID])

Avg. Order Value

SUM([Sales])/[#Orders]

along with Sales and Quantity.

These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.

The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields

LABEL:Days Since Last Order

IF [Days Since Last Order] <365 THEN [Days Since Last Order] END

This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix

Similarly, I created

LABEL:Years Since Last Order

IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END

which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.

The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter

pActiveCustThreshold

An integer parameter defaulted to 90.

We can then create

Is Active Customer?

[Days Since Last Order]<=[pActiveCustThreshold]

This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)

For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in

#Products

{FIXED [Customer ID]: COUNTD([Product ID])}

Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect

Sorting the Sort

We need another parameter to manage the sort.

pSort

I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings

I then created a calculated field to determine the measure to sort by

Sort By

CASE [pSort]
WHEN 1 THEN SUM([Days Since Last Order]) * -1
WHEN 2 THEN [#Orders]
WHEN 3 THEN SUM([Sales])
WHEN 4 THEN [Avg. Order Value]
WHEN 5 THEN SUM([Quantity])
END

Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.

In the table, amend the Customer ID pill to Sort by the Sort By field descending

Show the pSort parameter and test the functionality by switching the values.

Building the main viz

Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).

  • Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
  • Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order Date By Customer to the Tooltip shelf. Adjust Tooltip
  • Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
  • Remove all row & column borders and gridlines, zero lines etc.
  • Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
  • Hide the axes, hide field labels for rows.

Building the Viz in Tooltip

The Viz in Tooltip shows the Top 10 Products per Customer by Sales.

On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.

Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name

Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.

Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.

Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.

Now hide the Customer ID and Customer Name fields, and format the display.

Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.

Adding the Viz in Tooltip

Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>

Hovering on the circle mark should then display the Top 10 Products for that specific customer.

Adding the column headings

On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.

The help icon

Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.

I think that’s covered all the core points. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you find the needle in the haystack?

Donna ColesLeave a comment

It was Candra’s turn to ‘set’ the #WOW2021 challenge this week providing a hint in the challenge description that the solution would involve sets.

As with many challenges, I built the data out in tabular format to start with to verify I had all the components and calculations correct. The areas of focus are

  • Identify number of distinct customers per product
  • Identify overall average number of distinct customers per product
  • Identify if product above or below average distinct customers
  • Identify Top 50 products by Sales
  • Identify Unprofitable Products
  • Identify products that are both in the top 50 AND unprofitable
  • Building the viz

Identify number of distinct customers per product

To start off, add Product Name, Sub-Category, Category to the Rows shelf to begin building out a table. Add Sales (formatted to $k 0dp) and Profit (formatted to $k 0dp with negative values as () ) to Text and sort by Sales descending.

To identify the distinct customers per product, we can create

Customer Count per Product

{FIXED [Product Name] : COUNTD([Customer ID])}

Add this to the view.

Identify overall average number of distinct customers per product

What we’re looking for here is the average of all the values we’ve got listed in the Customer Count per Product column. Ie we want to sum up those values displayed and divide by the number of rows.

The number of rows is equivalent to the number of products, which we can get from

Count Products

{FIXED : COUNTD([Product Name])}

And so to get the overall average we calculate

Avg Overall Customer Count

{FIXED: SUM([Customer Count Per Product])} / [Count Products]

Add these fields to the view as well, so you can see how the values work per row. The last two calculations give you the same value across all rows.

Identify if product above or below average distinct customers

Given the above display, this is just a case of comparing values in 2 columns

Higher than Avg Customer Count

AVG([Customer Count Per Product]) > SUM([Avg Overall Customer Count])

this returns true or false – add this to the view too.

Identify Top 50 products by Sales

We can create a set for this. Right click on Product Name > Create > Set. Name the set something suitable eg Top 50 Products, and on the Top tab, state the number (50) and the field (Sales) and the aggregation (Sum)

Add this to the view, and if you’ve sorted by the sales, you should find the top 50 rows are all In the set, and the rest are Out.

Identify Unprofitable Products

We can use another set for this. Again create a set off of Product Name, call it Unprofitable Products, and on the Condition tab, set the condition so that the Sum of Profit is less than 0

Add this onto the view too.

Identify products that are both in the top 50 AND unprofitable

For this, we’re explicitly looking for the rows that are both In the Top 50 Products set and In the Unprofitable Products set.

We can use the Combined Set functionality to do this.

In the left hand data pane, select both the Top 50 Products and the Unprofitable Products sets (hold down ctrl to multi select), then right click and Create Combined Set. I called the set Products to Include, and select to combine the sets by including Shared members in both sets

If you then add this field to the Filter shelf, you will be left with just the 13 Products that match

This is the single filter field you can use as per Candra’s requirements.

Building the viz

To get the text to display to the left of the bar, you actually need to create a ‘fake’ bar chart.

  • Add Products to Include to Filter
  • Add Product Name to Rows
  • On the Columns shelf, double click and type in MIN(1)
  • Add Sales to Columns to the right of MIN(1)
  • Sort by Sales descending

Against the MIN(1) marks card

  • Change the Size to small
  • Set the Opacity of the Colour to 0% and the border to None
  • Add Product Name, Sub-Category and Category to the Label shelf and adjust accordingly, aligning left
  • Increase the height of each row to make the text visible

On the Sales marks card

  • Add Higher than Avg Customer to the Colour shelf and adjust
  • Show mark labels
  • Create a new field Profit Ratio : SUM([Profit])/SUM([Sales]) Format to % with 0dp and add to Tooltip
  • Add Profit, and Customer Count by Product to Tooltip and adjust accordingly

Finally, uncheck Show Header against Product Name and MIN(1) and Sales and format the borders/gridlines etc. Add the title, then add to the dashboard.

All done (I hope…)! My published version is here.

Happy vizzin’! Stay Safe!

Donna

How much do these states contribute to the total?

Donna ColesLeave a comment

By Week 20 of #WOW2020, Tableau v2020.2 was released, so guest challenger, Sean Miller, returned with a challenge to demonstrate one of the new bits of functionality that had been released – the ability to remove values from a set via dashboard actions.

Selected states on the map are highlighted and added to the list displayed on the right; clicking a state on the list then removed it from being highlighted on the map.

This felt like it should be straightforward, which was quite a relief after the last couple of weeks, and I’m hoping this blog doesn’t take too long to write 🙂

Spoiler alert! It was straight forward – hurrah! I do really value these ‘simpler’ challenges, when the main purpose is to introduce new features and functionality. In a business environment, it’s not always possible to work with the latest release, so having these challenges as a useful working example of a new feature to reference in future, is of great benefit.

So, if you hadn’t already realised, you’re going to need v2020.2 to complete this challenge, which you can get here , or you may need to use Tableau Public instead.

Creating the State set

Double-click State, double-click Sales and change mark type to Filled Map, and you’ve got your basic starting point 🙂

We want to be able to have different colours for the states depending on whether they’re in or out of a set of selected states.

Create a Selected States set by right clicking on State and selecting Create -> Set. Choose a random set of states.

Note – in v2020.2, the Selected States set is now just listed in the top left hand ‘dimensions’ pane (which also is now no longer labelled dimensions) with a ‘set’ icon to indicate its ‘data type’, rather than being listed in a separate ‘Sets’ section towards the bottom.

Drag this set onto the Colour shelf of the map. The states selected in the set will be coloured differently from the other states. Adjust the In/Out colour legend to suit.

Remove the map features

The displayed map in the solution is very ‘clean’; all labels,non US countries etc are hidden. This is set by selecting Map -> Map Layers from the top menu, and unchecking all the pre-selected Map Layer options.

Map Tooltip

The tooltip displays more measures than just the Sales, so we need some calculated fields

# Orders

COUNTD([Order ID])

# Customers

COUNTD([Customer ID])

Add these to the Tooltip shelf and adjust the display accordingly.

Creating the State List

There are several ways to build a basic list, but to get the formatting nicer, I created it using a bar chart as follows

  • Type in ‘Min(1)’ to Columns
  • Add State to Rows
  • Add Selected States to Filter shelf
  • Add State to Text shelf
  • Adjust Text to read ‘x <State>’ and format to white text, and align left middle

Adjust the colour of the bar to suit, then

  • Uncheck Show Header from the State pill in the Rows
  • Adjust the axis to be fixed from 0 to 1
  • Hide the axis.
  • Change the Tooltip text to ‘CLICK TO REMOVE’
  • Change the title to include the instruction

Invoke the Set Actions

Add both sheets to a dashboard.

Add a Change Set Values Dashboard Action to the Map sheet as below

As you click a state or select a group of states, it/they will be added to the list.

Add another Change Set Values dashboard action, this time based off the state list as follows

As you click on a state in the list now, it will disappear from the list and the state will no longer be highlighted on the map.

You will notice though, that as you click states on the map, your selections remain ‘selected’ until you click again. To fix this we need to use a little ‘true = false’ trick to ‘automatically deselect’ the states.

Automatically deselect states

Create new calculated fields called

True

True

False

False

and add both of these to the Detail shelf on the Map sheet.

Then back on the dashboard create a Filter action as follows

Building the Bar Charts

The above has completed the main part of the challenge and demonstrates the new feature, the option to Remove values from set in the Set Action dialog.

However Sean added some extra charts to the display.

Sales Chart

Add Sales to Columns and Selected Sales to Colour. Change Sales to be a Quick Table Calculation and set it to Percent of Total.

The tooltip and the chart title have additional information so we need

Total Sales

{FIXED:SUM([Sales])}

Sales of Selected States

{FIXED: SUM(IF [Selected States] THEN [Sales] END)}

% Sales of Selected States

[Sales of Selected States] / [Total Sales]

Format all these accordingly.

Add all these to the Detail/Tooltip shelf as required and amend Tooltip and Chart Title as necessary.

Orders Chart

This is pretty much the same as above….

Add # Orders to Columns and Selected Sales to Colour. Change # Orders to be a Quick Table Calculation and set it to Percent of Total.

The tooltip and the chart title have additional information so we need

Total Orders

{FIXED:COUNTD([Order ID])}

Orders of Selected States

{FIXED: COUNTD(IF [Selected States] THEN [Order ID] END)}

% Orders of Selected States

[Orders of Selected States] / [Total Orders]

Customers Chart

This doesn’t work quite the same (I only found out after the event), as a customer can order against more than 1 state it would seem. So while you’ll still need the following

Total Customers

{FIXED:COUNTD([Customer ID])}

Customers in Selected States

{FIXED: COUNTD(IF [Selected States] THEN [Customer ID] END)}

% Customers of Selected States

[Customers of Selected States] / [Total Customers]

we need to build it as a dual axis, with % Customers of Selected States on Columns alongside Min(1)

This should be all the building blocks needed. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a MoM Progress Report?

Donna ColesLeave a comment

Ann set the challenge this week, which for seasonsed #WoW participants like myself, was relatively straightforward. However it encapsulates a couple of concepts that I’ve only learnt from doing #WoW challenges over the years, so if you’re a relatively newbie, this is a great challenge for giving you a firm foundation for the future.

Determining the dates to compare

To start off, Ann stated that we needed to compare Oct 2019 with Nov 2019 but not to hard code the dates. I therefore used a parameter to drive this, which I created by first creating a new calculated field

Order Date MY

DATE(DATETRUNC(‘month’,[Order Date]))

Against every row, this sets the date to be 1st of the month.

I then created a parameter Order Date MY Parameter by right-clicking on the Order Date MY field and selecting Create -> Parameter. This has the effect of automatically populating the parameter with all the month/year options. I also set the format of the date to be March 2001 (ie month year), and the default value to be November 2019

I then created the following fields :

Current Month

[Order Date MY Parameter]

Previous Month

DATE(DATEADD(‘month’,-1,[Current Month]))

These two fields would ultimately drive the rest of the calculations I needed.

Sales by Sub-Category KPI

Ann is a fan of capital letters, so first up, you’ll need to create a field to store the Sub-Category in uppercase :

Sub-Cat UPPER

UPPER([Sub-Category])

Now for each KPI column, we need to determine

  • Sales for Current Month
  • Sales for Previous Month
  • % difference between the two
  • An indicator whether the % difference is less than 100% or not

Sales Current Month

IF [Order Date MY] = [Current Month] THEN [Sales] END

Sales Previous Month

IF [Order Date MY] = [Previous Month] THEN [Sales] END

% Sales

SUM([Sales Current Month])/SUM([Sales Previous Month])

formatted to a percentage with 0 dp

Sales Indicator

IF [% Sales] < 1 THEN 0 ELSE 1 END

You might initially think to set this to a string of ‘under’ and ‘over’ or similar, but setting to 1 and 0 will be useful later on when we need to calculate the Overall KPI.

Build the Viz

To create the ‘cell block column’ layout, we need to make use of an old friend Min(1) to create an axis, which allows us a bit more flexibility. This is the first of the techniques I was first introduced to via #WoW, and is a valuable concept to have in your arsenal.

You can already see the makings of the column just with these 2 pills. We then need to apply the following

  • Set mark type to be Bar.
  • Add Sales Indicator to Colour shelf, and adjust colours to match
  • Add Current Month, Previous Month, Sales Current Month, Sales Previous Month and % Sales to Tooltip and format tooltip to match the requirement.
  • Edit Axis and fix axis to start at 0 and end at 1, remove the title, and set tick marks to None
  • Duplicate the MIN(1) pill (click the pill, hold down Ctrl and drag to sit next to it). Make this pill dual axis and sync
  • Edit the top axis and change the title to SALES, and again set tick marks to None.

Orders by Sub Category KPI

As with the Sales KPI, we again need to know 4 things for this KPI

  • Number of orders in current month
  • Number of orders in previous month
  • % difference between the two
  • an indicator whether the difference is less than 100% or not

Orders Current Month

COUNTD(IF [Order Date MY] = [Current Month] THEN [Order ID] END)

Orders Previous Month

COUNTD(IF [Order Date MY] = [Previous Month] THEN [Order ID] END)

% Orders

[Orders Current Month]/[Orders Previous Month]

formatted to percentage to 1 dp

Orders Indicator

IF [% Orders] < 1 THEN 0 ELSE 1 END

The viz is then built exactly as above, adding further MIN(1) pills to the Columns shelf.

Units by Sub-Category KPI

Once again 4 fields are required, which are pretty identical to the ones defined for Sales above, but where any reference to Sales is replaced by Quantity.

Then build the column using further MIN(1) pills again.

Overall KPI

The overall KPI provides a % score based on the % of the 3 measures that are on target (ie 100% or more), along with an indicator (red circle), if the overall KPI < 100%.

Overall Score

[Qty Indicator] + [Orders Indicator] +[Sales Indicator]

This is where using 1 & 0 as the indicator value rather than a string value becomes handy. We can simply sum the three outputs together, giving an output of 0, 1,2 or 3.

% Overall Score

[Overall Score]/3

formatting to percentage set to 0 dp.

Overall Indicator

IF [% Overall Score] < 1 THEN ‘●’ END

This is where the second technique that was introduced to me originally by #WoW comes in : using geometric shapes in Text fields.

I use this concept A LOT in my work dashboards. I use this website to copy the shapes from.

Building the Viz

This time, we need to use Min(0.5), and set the Mark type to Text, adding Overall Indicator and % Overall Score to the Text shelf. The text is the formatted so the circle indicator is coloured red.

Using the font colour as a way of ‘colouring shapes’ is another concept I first discovered during #WoW, coupled with the fact that the field only displays a circle based on meeting some conditional logic. So while the Text field looks like a red piece of text (ie the circle) is visible, it won’t in fact always be there.

The axis is again fixed from 0 to 1, so this makes the text centred, and the Toolip is formatted to match requirements.

A dual axis is again required, but this time, the mark of the 2nd axis should be set to be a circle, with the size set to be the smallest possible. The colour should also be adjusted to 0 transparency (basically this mark needs to disappear). There should be no pills on the marks card of the secondary Min(0.5) axis.

And at this point, the viz should be pretty much complete (subject to any further formatting of fonts and row/column/grid lines – you might want to format the column lines to be white to break up the display, and adjust the size of the bars).

My published version is here.

Happy vizzin’!

Donna

Corey’s Table Challenge

Donna ColesLeave a comment

I’m going to attempt to be really brief this week, as I have very little time to get this drafted before I head off on my holibobs for a couple of weeks (so I’ll be off grid for the next couple of #WorkoutWednesdays but will attempt to catch up when I return).

This week’s challenge was set by IronViz 2018 finalist, Corey Jones, and focuses on formatting a table – full challenge details here.

Presenting data in Tableau in an ‘out of the box’ table chart type, does not allow row level formatting in the manner described in this challenge (Tableau is not Excel you know :-))

So you have to be creative, and this is what this challenge is all about.

Corey gives the clue that no more than 3 sheets are allowed, hinting that the above presentation is not all on one view, but at least 2 carefully arranged side by side on a dashboard.

I had 3 views in my dashboard – the shaded table of sub-category and measures, the stacked bar of % of region sales vs rest, and the title.

Now I ended up with 3 including the title, as I used the [Region] field as a quick filter throughout my build, and so to enable the title to change dynamically I needed to create a sheet so I could reference that field. Having looked at Corey’s solution later on, he chose to use a parameter to drive the Region selection. Parameters can be referenced in dashboard titles, so in Corey’s solution, the Sub Category list on the left hand side is also a view, whereas I combined with the measures. The only thing I couldn’t achieve with the combination was being able to left-align the Sub-Category heading, while leaving the other headings (Sales, Profit etc) centre-aligned.

The formatted table

The magic ingredient in building formatted tables is using an axis with labels. In this instance the field MIN(1) is your best friend.

  1. Add [Sub-Category] to rows
  2. Type MIN(1) into columns
  3. Set mark type to bar
  4. Add SUM([Sales]) to the Text shelf and right align
  5. Fix the axis from to start from 0 and ends at 1

and you get…

… something that looks pretty tabular, but you now have more flexibility with formatting styles.

This concept forms the basis for the whole table, but we need to step back quickly, as we need to set up some calculated fields.

Sales, Profit & Quantity are already defined in the dataset, but we also need

Profit Ratio

SUM([Profit]) / SUM(Sales)] formatted as a percentage to 0 decimal places

Total Sales for Year & Category

{FIXED YEAR([Order Date]), [Sub-Category]: SUM([Sales])}

This is a Level of Detail (LoD) calculation that stores the total sales for the year (2018) and subcategory, regardless of the [Region] filter being selected

% Sales for Selected Region

SUM([Sales])/SUM([Total Sales for Year & Category]) formatted as a percentage to 0 decimal places

Note – Technically this field will return % sales if any filter is applied, not just region

Highlight

[% Sales for Selected Region] > ([Highlight Threshold]/100)

This returns true where the % sales is more than the threshold selected by the user by entering a value into the [Highlight Threshold] parameter

The [Highlight] field can now be used to format the ‘table’ by adding to the Colour Shelf, setting true to be grey and false to be white.

Making the text bold

My solution differs from Corey’s. I chose to create the following calculated fields

LABEL : Sales Bold

IF [Highlight] THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is true.

LABEL: Sales Normal

IF NOT([Highlight]) THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is false.

Both these fields are placed side by side on the Label shelf, and with the ‘bold’ field formatted to be bold. As no row will ever have a value set in both fields at the same time, only a single value is displayed

Showing the column title at the top

I did this by creating an additional axis MIN(0), and applying dual axis which I synchronised. I removed all the pills from the MIN(0) marks card, and the [Measure Names] that had been automatically added to the colour shelf on the MIN(1) marks card.

This gave me an axis at the top which I could then edit – I changed the title to Sales, and set the axis tick marks to None for both major & minor ticks marks. On the bottom axis, I also had to edit, in this case setting the title to nothing as well as setting the axis ticks to none.

The height of the axis was also adjusted to make it appear closer and the font formatted accordingly. The Sub-Category was also set to not ‘Show Header’.

To create the other columns, the process is repeated with further dual axis MIN(1) & MIN(0) fields added to the columns, with further bold/normal Label fields also created.

The More Fields to the bottom left lists all the MIN(1) & MIN(0) cards in order.

To get the ‘column’ line to appear between the sub-category and sales columns, I set the transparency of the MIN(0) card associated to the Sales measure back to 100%, and it gave the appearance of a solid line.

Stacked Bar Chart

This is also a dual axis chart, which plots the % Sales for Selected Region field alongside our new friend MIN(1), where the MIN(1) axis is set to just over 1.

  • The Measure Names colours are set to grey (% Sales) and white (MIN(1))
  • The MIN(1) axis is set to the back
  • % Sales for Selected Region is the label on the associated card, and left aligned
  • A new field which is basically 1 – [ % Sales for Selected Region] is added to the label of the MIN(1) card and right aligned.
  • A border is applied to the bars via the Colour shelf

To create the ‘column heading’ a new field is required

LABEL: Bar

[Region] + ‘ vs. All Other Regions’

This is then added to the column shelf to get

so when the Region filter changes, the title changes too.

Both these sheets are then positioned carefully side by side on a dashboard, setting the Fit to Entire View. The padding of the table sheet is adjusted so it has 0 outer padding on the right, whilst the padding of the stacked bar is adjusted so it has 0 outer padding on the left.

And finally, the Region filter is added to the dashboard, set to floating positioned just near to the heading of the stacked bar. It’s size is also adjusted so only the arrow part remains.

There’s a fair few other little formatting bits and bobs to get the text right, remove columns etc, but these are hopefully settings you already know about. Feel free to contact me if you can’t figure something out.

My solution is available here.

Happy vizzin!

I’m off for some sunshine 🙂

Donna