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
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
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?
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
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |