SUMX with additional conditions in SSAS tabular

[R0bson]

Hi,

I have to prepare calculation like:

case when [perfo cost]/[sales net]>[bdg cos %] and date>='2021-06-01' and channel in ('facebook', 'affiliate') then [sales net]*[bdg cos %] else 'perfo cost' end as cost attribution 

This is what i have now:

cost attribution = SUMX(VALUES('Calendar'), if([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601,[sales net]*[bdg cos %],[perfo cost]))
I used SUMX because i got incorrect result in total. It works well for all data but I need calculate it only for channel facebook and affiliate.

I tried few options:

SUMX(VALUES('Calendar'), if([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601 && (MIN([Channel])='facebook' || MIN([Channel])='affiliate') ,[sales net]*[bdg cos %],[perfo cost]))
but it works only if i have filtered channel in Report. If i clean channel filter condition doesn't work for all data and i get incorrect total sum.

SUMX(VALUES('Calendar'), if(CALCULATE([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601,FILTER('Channels',OR([Channel]='facebook',[Channel]='affiliate'))),[sales net]*[bdg cos %],[perfo cost]))

it works well when i have filtered Channel. When i Clean channel filter Condition [sales net]*[bdg cos %] works for all data (not only facebook and affiliate) and also i get wrong total sum.
So i don't know how to put condition for channels that will be not sensitive for Channel Filter in Report.

Thank for help

[R0bson]

it gives null values for all data:

CALCULATE(SUMX(VALUES('Calendar'), if([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601,[sales net]*[bdg cos %],[perfo cost])),FILTER(Channels,([channel]="Affiliate" &&[channel]="facebook"))) 

I also tried with formula:

SUMX(SUMMARIZE(VALUES('Channels'),'Channels'[Channel] ,"AD", if([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601 && (MIN('Channels'[Channel])="Facebook" || MIN('Channels'[Channel])="Affiliate"),[sales net]*[bdg cos %],[perfo cost]),"FK",[perfo cost]),MIN([AD],[FK]))

It works correct for all data when i have filtered date >= 20210601, when i have include earlier dates total sum is wrong.

[SanketBhagwat]

Hi @R0bson .

You can add a FILTER statemen at the end of the DAX.

You can write like ,FILTER(Channels,[channel]="Affiliate" && [channel]="facebook").

Regards,

Sanket Bhagwat.

If this answers your question, then please do mark it as "Accept as Solution' so that other members could find it easily.

[R0bson]

Hi SanketBhagwat,

Thanks for reply, you are taking about:

CALCULATE(SUMX(VALUES('Calendar'), if([perfo cost]/[sales net]>[bdg cos %] && min(Calendar[DateID])>=20210601,[sales net]*[bdg cos %],[perfo cost])),FILTER(Channels,OR([channel]="Affiliate",[channel]="facebook")))  ??

It works only if i have filtered channel Affiliate or facebook, for Others Channel i have null values. If i clean channel filter i get total sum with only affiliate and facebook values.

[SanketBhagwat]

Why are you using OR in here

FILTER(Channels,OR([channel]="Affiliate",[channel]="facebook"))) ?

Try with the formula I mentioned and if it is still not working then please cross check your data once.

See if data is filtered for that particlar field.

Also if you could share the data and the output you need, then I can see and tell.

Regards,

Sanket Bhagwat