Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ChanelZheng
Frequent Visitor

Help on DAX measure that iterates over a table

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

1.PNG

 

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

2.PNG 3.PNG

 

The table to iterate over looks like this:

4.PNG

 

The output chart should look like:

5.PNG

 

Any help will be appreciated! Thanks

2 REPLIES 2
dax
Community Support
Community Support

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.

 

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?

1.PNG

 

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)

2.PNG

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.