topic Re: SUMX with additional conditions in SSAS tabular in DAX Commands and Tips

SUMX with additional conditions in SSAS tabular

R0bson — Wed, 16 Jun 2021 08:54:00 GMT

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

Re: SUMX with additional conditions in SSAS tabular

Anonymous — Wed, 16 Jun 2021 09:28:51 GMT

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.

Re: SUMX with additional conditions in SSAS tabular

R0bson — Wed, 16 Jun 2021 10:29:48 GMT

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.

Re: SUMX with additional conditions in SSAS tabular

Anonymous — Thu, 17 Jun 2021 05:28:19 GMT

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

Re: SUMX with additional conditions in SSAS tabular

R0bson — Thu, 17 Jun 2021 07:27:54 GMT

it gives null values for all data:

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.