5 Replies Latest reply on Jan 10, 2019 11:09 AM by Jeffrey Crowe

# Addition of a measure where the dimension is a specific value

I'm not sure exactly how to describe what I am trying to do but I think this logic question will help.

I want to get the percent of fresh fruit by comparing it to all fruit forms, fresh fruit, frozen fruit, and fruit drinks, by each fruit type.

So, if I have:

 Fruit Type Form Qty Bananas Fresh 10 Bananas Frozen 20 Bananas Drink 20 Apples Fresh 100 Apples Frozen 10 Apples Drink 10

Fresh Percentage = Fruit(Fresh) Qty / (Fruit(Fresh) Qty + Fruit(Frozen) Qty + Fruit(Drink) Qty

Bananas = 10 / (10+20+20)

Bananas = 10 / 50

Bananas = .2 (20%)

Apples = 100 / (100+10+10)

Apples = 100 / 120

Apples = .833 (83%)

I want to end up with this:

 Fruit Fresh Percentage Bananas 0.2 Apples 0.833

How do i specify to add the qty fresh + qty frozen.  How do I stipulate which "Form" I want to add together?

I'm sure I'm doing this wrong but I can't think of how to do it right.

Thanks!

• ###### 1. Re: Addition of a measure where the dimension is a specific value

Hi,

You can write a calculated field as

SUM(if [Fruit Type]='Fresh' then [Quantity] END)

/

SUM({Fixed [Fruit]: SUM(Quantity)})

• ###### 2. Re: Addition of a measure where the dimension is a specific value

Hi Jeffrey,

1) Create  a calculated field like below

IF [Form]="Fresh"

THEN

[Qty]

END

2)  Create a calculated field for percentage:

SUM([Fresh Value])/SUM([Qty])

3) Drag Fruit to Rows and Fresh Percentage to text

Hope this helps.

Plz mark this answer as correct or helpful to close the thread

BR,

NB

• ###### 3. Re: Addition of a measure where the dimension is a specific value

Maneesh,

This did not work with my data for some reason, it returned "Null", my data is pretty basic and I followed the format.  Not sure why it didn't work but the response from Naveen did work well.  I'd be interested in getting yours to work with one calculated field.

• ###### 4. Re: Addition of a measure where the dimension is a specific value

Perfect, thank you!

• ###### 5. Re: Addition of a measure where the dimension is a specific value

Maneesh,

I realized one of my "types" has a null qty.  How could I handle "drink" being null?

Thanks!