Formula DAX such as COUNTIF in Excel

[topalych]

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!

[Anonymous]

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

[topalych]

@Anonymous @Vvelarde

Thank You for answer. But it didn't work( 

I have data from different tables.

http://prntscr.com/czzjss

http://prntscr.com/czzlpp

And how can I add screenshots here?)

[Anonymous]

Hi @topalych,

Can you share us some sample data to test?

>>And how can I add screenshots here?

You can use the camera button to insert the picture.

Regards,

Xiaoxin Sheng

[topalych]

Yes, course!

What eMail?

[Anonymous]

Hi @topalych,

You can upload to 1drive and share a link.

Regards,

Xiaoxin Sheng

[topalych]

https://1drv.ms/u/s!AvZynvEmOhwzhOodKAlV__qc3j_Hig

[topalych]

@Anonymous, Did You get the file?

[Anonymous]

Hi @topalych,

Yes, I download it. But I faced a trouble(complex relationship) to merge multiple table columns to one and write a measure to calculate them.

Regards,

Xiaoxin Sheng

[topalych]

Thank You anyway!)

[Vvelarde]

@topalych

Hello, use this measure

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