% of total help

[bondra76]

Could use some help – I tried to simplify this down to explain

I am using powerpivot and have three different types of fruits. I have set up measures to total the fruit, but I need to create a calculated column that determines what % of the quantity of each fruit is on each line

The bolded valyes are what I am looking to accomplish. For example, in the first line the quantity of apples is 2, which makes up 50% of the total number of apples.

Is this possible to achieve this with a formula?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Qty[/TD]
[TD]% of Fruit[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]2[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]1[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]1[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]3[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]2[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Apples:4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Oranges: 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Pears: 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[t0azt]

=B3/RIGHT(INDIRECT(ADDRESS(MIN(IF(NOT(ISERROR(FIND(A3,$A$9:$A$11,1))),ROW($A$9:$A$11),"")),1)),1)

Entered with CTRL+SHIF+ENTER

 

[akice]

= Table[Fruit] / CALCULATE ( SUM( Table[Qty] ), ALLEXCEPT( Table, Table[Fruit] ) )

 

[bondra76]

= Table[Fruit] / CALCULATE ( SUM( Table[Qty] ), ALLEXCEPT( Table, Table[Fruit] ) )

Thanks I think this got me there - appreciate the response!