7 Replies Latest reply on Apr 22, 2018 11:28 PM by Elmurat Bavlankulov

# How to show dimension as additional column to Measure Names columns

Hello All,

I have a simple question.

In my table I have Products, Quantities, Profits and States. Please see below copy from Excel.

So first two column will show Quantity and Profit for each product, and for the next columns I need to show from which states were the orders from and their percentage share on Total Profit for this product.

 Quantity Profit Profit % Profit % Profit % Profit % Profit % Profit % Profit % Profit % Profit % Product Name Total Total State 1 State 2 State 3 State 4 State 5 State 6 State 7 State 8 State 9 Product Name 1 62 4,777 0% 21% 0% 34% 27% 14% 3% Product Name 2 78 1,475 7% 6% 1% 0% 18% 0% 13% 53% 2% Product Name 3 17 4,431 0% 20% 0% 1% 21% 0% 39% 15% 4% Product Name 4 43 4,359 2% 14% 1% 49% 0% 0% 23% 8% 3% Product Name 5 24 3,958 60% 12% 1% 0% 0% 1% 10% 12% 5% Product Name 6 77 4,731 2% 48% 0% 40% 0% 2% 0% 7% Product Name 7 78 2,512 0% 49% 1% 41% 0% 1% 8% Product Name 8 10 3,564 11% 19% 2% 1% 49% 12% 1% 6% Product Name 9 25 4,019 0% 49% 3% 1% 19% 12% 14% 2% Product Name 10 99 1,837 9% 0% 34% 52% 3% 3% Product Name 11 35 3,733 20% 0% 1% 0% 28% 30% 17% 4% Product Name 12 87 4,767 1% 40% 0% 1% 0% 53% 0% 4% Product Name 13 83 3,278 11% 1% 1% 1% 5% 78% 4% Product Name 14 30 4,786 0% 24% 32% 2% 32% 5% 5% Product Name 15 38 2,745 3% 18% 0% 0% 0% 25% 40% 9% 4% Product Name 16 61 4,688 0% 27% 1% 41% 1% 1% 10% 12% 5% Product Name 17 86 1,307 28% 1% 31% 1% 0% 34% 0% 4% Product Name 18 97 2,252 23% 0% 19% 28% 27% 2% Product Name 19 25 4,123 0% 19% 0% 32% 0% 36% 6% 7% Product Name 20 24 3,105 2% 11% 0% 1% 17% 0% 56% 13% 2%

• ###### 1. Re: How to show dimension as additional column to Measure Names columns

hi Elmurat,

So the problem with just using the SUM aggregation on Sales, is that Tableau (correctly) calculates the Sum for each level of detail in the Viz (Product/State) so you get multiple rows for each product SUM of Sales. This is where we can use Table Calculations. In the attached I've created this

WINDOW_SUM(SUM([Sales]))

and made it discrete (blue). I bring this in and set the compute to Table Across (assuming your data view is laid out as per the image)

Hope that helps

1 of 1 people found this helpful
• ###### 2. Re: How to show dimension as additional column to Measure Names columns

Hi,

Looks like you need to pivot the state columns.

~Tushar

• ###### 3. Re: How to show dimension as additional column to Measure Names columns

Thank you very much Simon. This really helped.

• ###### 4. Re: How to show dimension as additional column to Measure Names columns

Hi Tushar. I will check this option soon. But I think it will work slowly because I use huge dataset, and if I do pivoting I think it will slow down the dashboard. What do you think?

• ###### 5. Re: How to show dimension as additional column to Measure Names columns

Hello Simon.

I have one additional question to your solution. I have to add count of orders for each product. So I have Order ID, which is unique for each order, so when I want to create same header like Total Profit and Total Quantity (using WINDOW_SUM and SUM), it doesn't let me do that, because it is string value. I tried to use ID as measure and Count Distinct, it didn't work and tried to use Include LOD, but it didn't work either. Can you please help me?

• ###### 6. Re: How to show dimension as additional column to Measure Names columns

You should be able to do a WINDOW_SUM on a COUNTD...so like this

WINDOW_SUM(COUNTD([Order ID]))

and then bring this in as before.

I the attached example, it is doing a COUNTD for each segment, so we'd get 3 values and then the WINDOW_SUM is adding these 3 numbers together to get the total for the header.

Just a note that the addition of this column (especially at Product Level) will likely be higher than the number of all orders...as an order could feature multiple products.

1 of 1 people found this helpful
• ###### 7. Re: How to show dimension as additional column to Measure Names columns

Thanks a lot Simon!