Add columns that contain filtered sums from another table

[Anonymous]

I have a table of campaigns. I want to add columns to my campaign table that contain the number of opportunities at each sales stage. I have a table of opportunities with columns for stage number and stage name. 

Campaigns

Opportunities

These two table have a relationship

How can I add columns to the campaigns table that summarize the number of opportunities at each stage? I would add a column for each of the 9 sales stages: Investigate, Meet, Probe, Apply, Convince, Tie it up, Closed Won-Pending, Closed Won, Dead. 

Thanks in advance for your help.

Jeff

[TomMartens]

Hey,

I think you are looking for the LOOKUPVALUE function: https://dax.guide/lookupvalue/

If you have to aggregate multiple rows from the source table you can use something similar to this:

column name = 
var thisCampaignID  = '<campaigntable>'[campaignid]
return
CALCULATE(
	SUM('<opportunitytable>'[colums])
	FILTER(
		'<opportunitytable>'
		, '<opportunitytable>'[campaignid] = thisCampaignID
	)
)

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous · Accepted solution]

I can't test it right now, but you have a bidirectional relationship, so you can make use of the RELATEDTABLE.

When you are in the CAMPAIGN table, the RELATEDTABLE function will return the rows in the OPPORTUNITY which are filtered, for each row.

So if you add a calculated column and use this code

rows=countrows(RELATEDTABLE(opportunity))

it will tell you the number of connected rows.

Now the trick is to filter them based on your stage, so (again without testing but should work), something in the line of 

Rows in Stage Investigation=countrows(FILTER(relatedtable(opportunity);Stage="investigation"))

or probably better

Rows in stage investigation = calculatetable(opportunity;Stage="Investigation")

[Anonymous]

Thanks, Tom. I'm not sure that does what I need. 

From the opportunity table I need to count the rows that contain a particular value from the stage number column. and put that value into a column into a row the campaigns table, based on the campaign ID. 

Where would I apply the Dax you suggested? Would that be used to create a custom column in the quiery editor? Or would I use that to create a new column in the report?

[TomMartens]

Hey, consider to create a calculated column. This article explains it very detailed: https://www.jetglobal.com/blog/calculated-columns-vs-measures/

Regards,
Tom