5 Replies Latest reply on Oct 21, 2018 9:45 PM by Mahfooj Khan

# Issue getting accurate % of total for visible data

Hello,

So I am working on try to get a % of total in a view I have, and I simply cannot get it to work. I have tried using the quick table calc along with creating a Calculated field to find the % but every iteration I have tried I simply cannot get any accurate data. I would attach the workbook but it contains confidential data.. Having said that I built the below scenario into a Sample workbook attached.

Data is structured as follows. It is a flat table(hyper made of 3 joins, Lets say table 1 is fruits, table 2 is plates, and table 3 is the one that correlates what fruits go on what plate).

So I have a worksheet that lists all the fruits, and as part of the worksheet I detail the number of plates a fruit is present in. Now I want to display the percentage of the total "fruit" present in the visible worksheet in each row.

Imagine in this scenario that there are 25 plates.

Example

Fruit     Plates     %

Apple     20

Kiwi        10

Grape     5

So what I want here is to display the % of the fruit based on total of 35 plates in this case. So 20/35 should display 57.14%. This should also work when using TopN.. (I am using index() <=10 to display top 10)

Any suggestion with the above scenario would be greatly appreciated.

Thanks

• ###### 1. Re: Issue getting accurate % of total for visible data

Hi Jose

Duplicate the plate measure and rename it like plate%.

Bring the duplicated measure into the view, Right Click->Quick table calculation-> Percent of total.

Now bring the measure also in text now you will get the exact distribution

Method2:

Create calculated field -> SUM(Plate)/TOTAL(SUM(Plate))

Set default properties for this calculation as Percentage and bring this into the view

• ###### 2. Re: Issue getting accurate % of total for visible data

I have tried both of these, and they do not provide the necessary results.

• ###### 4. Re: Issue getting accurate % of total for visible data

So this seems to work to an extent. Can you think of a way to apply this to only look at the rows displayed in the worksheet? I am leveraging Index() <= 10 to limit it to the top 10.

• ###### 5. Re: Issue getting accurate % of total for visible data

Hi,

You may try this, Top 10: INDEX()<=10

While using INDEX you need to set up the table calculation like above screenshot

You can also use RANK() like this: RANK(SUM([% of Total]),'desc')<=10

Just drag it to filter shelf and select "True", compute using table down.

Hope this will help.

Mahfooj

1 of 1 people found this helpful