Subtract a value from one category and add to another

[jkoclejda]

Hi!

I have a table like below and I want to subtract 40% from the quantity of the pears and add 10% to the quantity of the apples.

Fruit Type	Quantity
Pears	20
Cherries	10
Bananas	40
Apples	15
Summary	85

It's just an egxample - in fact i must do it with sessions for source / medium from Google Analytics but but I figured it would be easier to explain. Coming back to the fruit example, I wrote the following calculation:

Fruit Quantity =

VAR _Pears = CALCULATE((SUM('Fruits'[Quantity]) * 0.4), 'Fruits'[Fruit Type] = "Pears")

VAR _Apples = CALCULATE((SUM('Fruits'[Quantity]) * 0.1), 'Fruits'[Fruit Type] = "Apples")

RETURN

CALCULATE(

SUM('Fruits'[Quantity]) - _Pears +_Apples

)

I thought the quantity would only be added / subtracted for the fruits specified earlier in the variables. Unfortunately, this changes the quantity for all fruits.

Does anyone have an idea how to write such a calculation that only the quantity of apples and pears change, and the rest remain original?

[SpartaBI · Accepted solution]

@jkoclejda try this for a quick fix to your formula:

Fruit Quantity = 
VAR _Pears = CALCULATE((SUM('Fruits'[Quantity]) * 0.4), KEEPFILTERS('Fruits'[Fruit Type] = "Pears"))
VAR _Apples = CALCULATE((SUM('Fruits'[Quantity]) * 0.1), KEEPFILTERS('Fruits'[Fruit Type] = "Apples"))
RETURN
CALCULATE(
SUM('Fruits'[Quantity]) - _Pears +_Apples
)

Showcase Report – Contoso By SpartaBI

      

[jkoclejda]

It works perfect! Thank you very much 🙂

[SpartaBI]

@jkoclejda my pleasure 🙂