Forum Discussion

topalych's avatar
topalych
Frequent Visitor
9 years ago

Formula DAX such as COUNTIF in Excel

Hello! Help me please. 

I need formula DAX.

There is a table:

 

Campaign    Cost     ClientID

First            900        0001

First            900        0002

First            900        0003

Second       1000       0004

Second       1000       0005

Third           300        0006

 

I spent 2200 USD on three campaigns. 900 USD on "First", 1000 USD on "Second" and 300 USD on "Third".

And I got 6 Clients. I want to see how much I spent to get each Client.

 

What should I do to see 300 USD in "First"? Because it repeats 3 times.

The same thing with "Second". There should be 500 USD. Cause I got 2 clients from that Campaign and spent 1000 USD.


In Excel, it is easy to make))

=B2/COUNTIF(A:A;A2)

 

I'll be very appreciate for help!

 

 

10 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    topalych

     

    Hello, use this measure

     

    CostCampaign = DIVIDE(CALCULATE(Min(Table1[Cost]));COUNTROWS(Table1))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi topalych,

     

    Vvelarde's formula seems well, you can also use blew formula:

     

    Measure:

     

    Current Cost = 
var currCampaign=LASTNONBLANK(Table1[Campaign],[Campaign])
Return
DIVIDE(MIN([Cost]),COUNTAX(FILTER(ALL(Table1),[Campaign]=currCampaign),[Campaign]))

     

     

    Regards,

    Xiaoxin Sheng