The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
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
Thank You for answer. But it didn't work(
I have data from different tables.
And how can I add screenshots here?)
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
Yes, course!
What eMail?
Hi @topalych,
You can upload to 1drive and share a link.
Regards,
Xiaoxin Sheng
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
Thank You anyway!)
Hello, use this measure
CostCampaign = DIVIDE(CALCULATE(Min(Table1[Cost]));COUNTROWS(Table1))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
142 | |
109 | |
107 | |
74 | |
61 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |