Help on DAX measure that iterates over a table

[ChanelZheng]

Hi 

I want to create a DAX measure that iterates over a table and calls other measures based on the row context, and I am not sure if this is achievable or not. 

More specificially, a measure that iterates over a year to generate a target based on different growth rate for the year and the team. 

The base table is the year

Multiple what if parameters that take user input of growth rate for each year and each team:

 

The table to iterate over looks like this:

The output chart should look like:

Any help will be appreciated! Thanks

[dax]

Hi ChanelZheng,

I am not clear  about your data structure, so if possible, could you please inform me in details(such as your data sample, and what is your team A % and a%)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChanelZheng]

Hi Zoe 

Thanks for getting back to me. I managed to work it out myself on the last question. 

But I am stuck on writing a dax that calculate the cumulative total. Can you please assist?

I have a year table and a crossjoin table with year and teams:

Team_and_year = 
var teams = 
{"Team A","Team B","Team C"}
return CROSSJOIN('Year', teams)

DAX for team_annual_target:

team_annual_target = 
var target = SELECTEDVALUE('CampaignGoal'[CampaignGoal]) -- target
var base_year = 2019 -- campaign base year
var current_selected_year = SELECTEDVALUE('Year'[Year]) -- selected year
var duration = MAXX(ALLSELECTED('Year'[Year]), 'Year'[Year]) - base_year + 1 -- number of years

var _2019_rate = SELECTEDVALUE('2019_Revised_Percentage'[2019])
var _2020_rate = SELECTEDVALUE('2020_Revised_Percentage'[2020])
var _2021_rate = SELECTEDVALUE('2021_Revised_Percentage'[2021])
var _2022_rate = SELECTEDVALUE('2022_Revised_Percentage'[2022])
var _2023_rate = SELECTEDVALUE('2023_Revised_Percentage'[2023])

-- team distribution
var team = SELECTEDVALUE(Team_and_year[Team])
var team_a = SELECTEDVALUE(CP_Percentage[CP])
var team_b = SELECTEDVALUE(RP_Percentage[RP])
var team_c = SELECTEDVALUE(BQ_Percentage[BQ])

-- yearly revised percentage used for calculation
var yearly_percentage = SWITCH(current_selected_year,2019,_2019_rate,2020,_2020_rate,2021,_2021_rate,2022,_2022_rate,2023,_2023_rate,0)
-- program percentage used for calculation
var team_percentage = SWITCH(team,"Team A",team_a,"Team B",team_b,"Team C",team_c,0)
-- annual target by each team
var team_target = (1 + yearly_percentage) * ((target * team_percentage) / duration)

RETURN
team_target

yearly_total:

year_total = 
var current_selected_year = SELECTEDVALUE('Year'[Year])
return
SUMX(FILTER(Team_and_year, Team_and_year[Year] = current_selected_year),Team_and_year[team_annual_target])

Thanks a lot!

Chanel