11 Replies Latest reply on May 25, 2016 10:55 AM by Mahfooj Khan

# Aggregating percentage on higher dimension

Hi,

I'm new to Tableau. Here is my problem statement:

I have nested categories: Portfolio->Area->Function->Project id. I've created one calculated field to calculate some percentage on most granular level (Project id). But when I replace Project id with Function it simply adds the percentage values. For example - there are 2 Project ids showing 60% and 70% but on Function level its showing 130% which is wrong. So I want to know if there is a way to dynamically calculate percentage based on the hierarchy of dimensions?

Hope i'm able to explain my problem precisely.

Thanks,

Mayank

• ###### 1. Re: Aggregating percentage on higher dimension

Mayank,  Please mock up your scenario in a workbook using the super store sales data set so we can help you.  This should only take you 5-10 minutes.  You can use their hierarchy of category/subcategory/product.

• ###### 2. Re: Aggregating percentage on higher dimension

So let assume I want to measure profit by sale for each row. It'll look like this (see last column). But when I replace row by product id, it give me sum of all 3 percentage values [-1.18 + ( - )1.18 + 14 = 11.65%] but I ideally it should be [( -4.8392 - -4.8391 + 33.8744)/(411.332+411.332+241.96) = 12.60%]

 Row ID Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment Country City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit Profit by Sale % 1761 CA-2012-130785 9/5/2012 9/9/2012 Standard Class AG-10900 Arthur Gainer Consumer United States San Diego California 92105 West FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 411.332 4 0.15 -4.8392 -1.18% 5495 CA-2011-105249 11/28/2011 11/28/2011 Same Day DH-13675 Duane Huffman Home Office United States San Francisco California 94122 West FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 411.332 4 0.15 -4.8392 -1.18% 6402 CA-2014-125472 5/31/2014 6/1/2014 First Class BD-11725 Bruce Degenhardt Consumer United States Lafayette Louisiana 70506 South FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 241.96 2 0 33.8744 14.00%
• ###### 3. Re: Aggregating percentage on higher dimension

somehow I could not paste all the columns

• ###### 4. Re: Aggregating percentage on higher dimension

Here is the twbx file:

• ###### 5. Re: Aggregating percentage on higher dimension

Hi Mayank,

You can attach the workbook by following steps.

Once you click on it, you will get Attach option at the bottom using which you can upload the workbook. It would be better if you delete the Document that you have created and upload the workbook here it self.

-Ashish

• ###### 6. Re: Aggregating percentage on higher dimension

Hi, Mayank Gupta

I'm new to Tableau too.

Possible reason is Error function formula.

Profit by Sale %<>[Profit]/[Sales]

Profit by Sale %=sum([Profit])/sum([Sales])

duyingtao

• ###### 7. Re: Aggregating percentage on higher dimension

Hi Ashish,

I'm not getting this option to attach the file.

• ###### 8. Re: Aggregating percentage on higher dimension

Thanks du yingtao ! But now its showing 2.27% on product id level. Its should be 12.60%.

• ###### 9. Re: Aggregating percentage on higher dimension

Hi,

I've a question if you see you've used two time minus Ideally It should be +(-). Just because of "- -" Its returning 33.8744 for Profit sum. That's why you're getting 12.60% at Product level.

I've attahced the screen shot of your post.

Profit: -4.8392+(-4.8392)+33.8744

Sum: 24.196

Sales: 411.332+411.332+241.96

Sum: 1064.624

Profit/Sales: 24.196/1064.624

2.27% at ROWID level

11.65% at Product level

Use below mentioned screen shot for your reference.

Compute using pane (down) in the copy formula. Grand Total is just for your understanding.

Modified workbook attached (version 9.3). Have a look.

Let me know If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 10. Re: Aggregating percentage on higher dimension

You are right, it was my bad. I was doing wrong calculation in excel. Apologies. Thanks Mahfooj Khan and du yingtao.

This thread can be considered close.

• ###### 11. Re: Aggregating percentage on higher dimension

Kindly mark the answer as correct to close the thread logically. So that It may help others who have similar kind of queries.