topic Subtract part of value from category X and add to category Y in DAX Commands and Tips

Subtract part of value from category X and add to category Y

jkoclejda — Tue, 18 Oct 2022 13:05:05 GMT

Hi!

I have table Traffic with data like below:

Source	Sessions
Google Ads	5000
Organic	4500
Brand	3000
Social Media	1000

I need to substract 40% of sessions from Brand and add this value to Organic.

I figured out how to make the first part:

VAR _Brand = CALCULATE((SUM('Traffic'[Sessions]) * 0.4), KEEPFILTERS('Traffic'[Source] = "Brand"))

RETURN

CALCULATE(

SUM('Traffic'[Sesje]) - _Brand

But I have no idea how to "change" the source of the value received from _Brand variable and add it to the Organic. Or maybe I should do it in a completely different way than I started?

The result should be such values

Source	Sessions
Google Ads	5000
Organic	5700
Brand	1800
Social Media	1000

I will be grateful for all the tips.

Re: Subtract part of value from category X and add to category Y

Jihwan_Kim — Tue, 18 Oct 2022 13:31:50 GMT

Hi,

Please try the below measure whether it suits yours requirement.

Sessions expected outcome measure: = VAR _Brand = CALCULATE ( SUM ( 'Traffic'[Sessions] ), FILTER ( ALL ( Traffic ), Traffic[Source] = "Brand" ) ) * 0.4 RETURN SWITCH ( SELECTEDVALUE ( Traffic[Source] ), "Brand", SUM ( 'Traffic'[Sessions] ) - _Brand, "Organic", SUM ( 'Traffic'[Sessions] ) + _Brand, SUM ( Traffic[Sessions] ) )

Re: Subtract part of value from category X and add to category Y

Anonymous — Wed, 19 Oct 2022 06:10:57 GMT

Hi jkoclejda ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a column.

Column = VAR _brand = CALCULATE ( SUM ( 'Table'[Sessions] ) * 0.4, FILTER ( ( 'Table' ), 'Table'[Source] = "Brand" ) ) RETURN IF ( ( 'Table'[Source] ) = "Brand", ( 'Table'[Sessions] ) - _brand, IF ( ( 'Table'[Source] ) = "Organic", ( 'Table'[Sessions] ) + _brand, ( 'Table'[Sessions] ) ) )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Subtract part of value from category X and add to category Y

jkoclejda — Wed, 19 Oct 2022 08:22:39 GMT

Hi!

Your solution works perfect with data from my example. I tried to apply it to my real data - my table have much more records and columns. I got stuck trying to apply this to another column.

For example

Source Type	Source Group	Sessions
brand google ads	Brand	1000
brand direct	Brand	2000
organic	Organic	3000
campaign 1	Google Ads	4000

And when I try your solution with column Source Group everything is ok. But when it want to do it with column Source Type end substract from brand direct and then add to to organic it doesn't work (only subtraction works) . But when i do the same with brand direct and brand google ads everything is fine and I don't know why.

Re: Subtract part of value from category X and add to category Y

jkoclejda — Wed, 19 Oct 2022 08:25:14 GMT

Hi! Thank you for the answer but i have a question. Why there is 5 000 in total for Measure column instead of 13 500?

Re: Subtract part of value from category X and add to category Y

Jihwan_Kim — Wed, 19 Oct 2022 08:51:37 GMT

Hi,

Thank you for your feedback.

What is the expected outcome of the second example?

Source Type	Source Group	Sessions
brand google ads	Brand	1000
brand direct	Brand	2000
organic	Organic	3000
campaign 1	Google Ads	4000

Or, please share your complete sample pbix file' link with how your expected outcome looks like, and then I can try to look into it to come up with a more accurate solution.

Thanks.

Re: Subtract part of value from category X and add to category Y

jkoclejda — Wed, 19 Oct 2022 09:23:29 GMT

Hi,

Here is my sample pbix file - I reconstructed the issue that i have in my real data

Re: Subtract part of value from category X and add to category Y

Anonymous — Wed, 19 Oct 2022 09:15:32 GMT

Hi jkoclejda ,

5 000 in total for Measure column instead of 13 500

The measure is like the following:

Measure = var _brand=CALCULATE(MAX('Table'[Sessions])*0.4,FILTER(ALL('Table'),'Table'[Source]="Brand")) return IF(MAX('Table'[Source])="Brand",MAX('Table'[Sessions])-_brand,IF(MAX('Table'[Source])="Organic",MAX('Table'[Sessions])+_brand,MAX('Table'[Sessions])))

The total value returns the max('table'[sessions]', so the max value is 5000.

You can create another measure based on the measure.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Subtract part of value from category X and add to category Y

Anonymous — Wed, 19 Oct 2022 09:23:12 GMT

Hi jkoclejda ,

Please modify your measure.

sessions_f = VAR _Brand = CALCULATE ( SUM ( 'sample_data'[sessions] ), FILTER ( ALL ( 'sample_data' ), 'sample_data'[source_type] = "brand organic" ) ) * 0.4 RETURN SWITCH ( SELECTEDVALUE ( 'sample_data'[source_group] ), "brand", SUM ( 'sample_data'[sessions] ) - _Brand, "organic", SUM ( 'sample_data'[sessions]) + _Brand, SUM ( 'sample_data'[sessions] ) )

If I have misunderstood your meaning, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Subtract part of value from category X and add to category Y

jkoclejda — Wed, 19 Oct 2022 09:28:28 GMT

I must subtract sessions from "brand organic" in "source_type" column and add it to "organic". But on the report, in table will be source_group as column.