topic Re: Subtract a value from one category and add to another in DAX Commands and Tips

Subtract a value from one category and add to another

jkoclejda — Thu, 19 May 2022 15:12:09 GMT

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?

Re: Subtract a value from one category and add to another

SpartaBI — Thu, 19 May 2022 14:51:47 GMT

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

Re: Subtract a value from one category and add to another

jkoclejda — Thu, 19 May 2022 15:11:32 GMT

It works perfect! Thank you very much 🙂

Re: Subtract a value from one category and add to another

SpartaBI — Thu, 19 May 2022 15:14:26 GMT

jkoclejda my pleasure 🙂