Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
thanks
Solved! Go to 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"))))
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.
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"))))
unfortunately didn't work it's picking up correct values but the sum is not working for some reason
@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"))))
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
87 | |
77 | |
52 | |
37 | |
21 |