How to generate a distribution based bar chart on row_numbers

user_8318088 0
Viewed 22

I have a SQL query that acts as a data source in my tableau desktop:

SELECT
  row_number() over (order by sales) as rn,
  article_number,
  country,
  SUM(sold_items) as si,
  SUM(sales) as sales
FROM data.sales
WHERE sales.order_date between '2021-01-01' and '2021-12-31'
GROUP BY 2, 3

On tableau I dragged rn to column and sales to row to generate a bar chart. The following is the output:

distribution

I want to convert this into a 0-100% distribution chart so that I can get the following result:

enter image description here

How can I achieve this? Also, I want the user to filter by country level so even if the # of records increase or decrease, the distribution should always be consistent with the filtered data.

tableau-desktop
1 Answers

You can do this with nested table calcs.

For example, the following uses the Superstore sample data set, and then first computes a running total of SUM(Sales) per day, then converts that to a percent of total. Notice the edit table calc dialog box - applying two back to back calculations in this case.

enter image description here

The x-axis in this example is Order-Date, and in your question, the the x-axis is a percentage somehow - so its not exactly what you requested but still shows that table calcs are an easy way to do these types of operations.

Also, realize you can just connect to the sales table directly, the custom sql isn’t adding any value, and in fact can defeat query optimizations that Tableau normally makes.

The tableau help docs explains table calculations. Pay attention to the discussion on partitioning and addressing.

user_4419790
Related