Dax formula to select values if repeated with different tags

[miriammmattar]

if I have this table and I want a formula to calculate if values are in Actuality AC and B2 then calculate only AC  then add to it Values in Actuality B2 if they only exist in B2 so in this case :

Formula = sum (73694464 (AC) + 73690169 (B2) + 81663376 (B2) ) = 229048009

my formula adds only the B2 values = 240552837

this is my current formula but it's not working

Forecast = if(SELECTEDVALUE('Finance_AC&BU'[Actuality])in{"AC","B2"},CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]="AC"),if(selectedvalue('Finance_AC&BU'[Actuality])<>"AC",CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]<>("AC"))))

thanks

[v-robertq-msft]

Hi, 

Have you followed the DAX formula posted by amitchandak to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@miriammmattar ,

if(not(isblank(Countx(Filter('Finance_AC&BU','Finance_AC&BU'[Actuality] in {"AC","B2"}) , 'Finance_AC&BU'[Actuality]))) ,CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]="AC"),if(selectedvalue('Finance_AC&BU'[Actuality])<>"AC",CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]<>("AC"))))

[miriammmattar]

unfortunately didn't work it's picking up correct values but the sum is not working for some reason

[amitchandak · Accepted solution]

@miriammmattar , Try like

Sumx( Addcolumns( summarize( 'Finance_AC&BU', 'Finance_AC&BU'[Month year], 'Finance_AC&BU'[Actuality], 'Finance_AC&BU'[KPI NAme]) , "_mes" ,if(not(isblank(Countx(Filter('Finance_AC&BU','Finance_AC&BU'[Actuality] in {"AC","B2"}) , 'Finance_AC&BU'[Actuality]))) ,CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]="AC"),if(selectedvalue('Finance_AC&BU'[Actuality])<>"AC",CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]<>("AC")))) ),[_1])

or create the measure and then use that in above measure , in place of calculation

M1 =if(not(isblank(Countx(Filter('Finance_AC&BU','Finance_AC&BU'[Actuality] in {"AC","B2"}) , 'Finance_AC&BU'[Actuality]))) ,CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]="AC"),if(selectedvalue('Finance_AC&BU'[Actuality])<>"AC",CALCULATE(SUM('Finance_AC&BU'[Value]),'Finance_AC&BU'[Actuality]<>("AC"))))