Forum Discussion
Need help creating a for loop to calculate variable for each row
I want to calculate a variable for each year, based on the previous year and some user input (perhaps using whatif parameters).
Input parameters:
Percentage of women today (e.g. 12%)
Percentage of women desired (e.g. 50%)
Efflux of personnel yearly (e.g. 10%)
Current hiring policy (e.g. 70% women, 30% men)
The resulting column should look like this:
| Year | % women |
| 2019 | 12.00000 |
| 2020 | 17.80000 |
| 2021 | 23.02000 |
| 2022 | 27.71800 |
| 2023 | 31.94620 |
| 2024 | 35.75158 |
If it helps, this is the Python code to do it (may not be pretty but it works):
amount_w = int(input('What is the current percentage of women?'))
efflux = int(input('What is the efflux per year?'))/100
replace_w =int(input('What is the current hiring of women?'))
goal=int(input('What is the desired percentage of women?'))
replacement['Year']=[2019,2020,2021,2022,2023,2024]
replacement['Number of women'] = amount_w
replacement['Goal']=goal
def calculate(amount_w):
amount_w = (amount_w * (1 - efflux)) + ((100 * efflux)*replace_w)
return amount_w
for i in range(1, len(replacement)):
replacement.loc[i, 'Number of women'] = calculate(replacement.loc[i-1, 'Number of women'])
Can I do this in DAX? Or using the Python script in PowerBI? Please let me know if I need to add more info, it's my first time posting a question here.
5 Replies
v-xicaiCommunity Support
Hi Anonymous ,
Could you please clarify what's the logic to get your desired output using current data sample?
Best Regards,
Amy
Sure v-xicai!
It's supposed to be a calculator for how long it takes to reach a 'diversity goal' such as the amount of women in leadership positions.I work with a theoretical 100 people. Every year the same amount of people leave and are replaced. First you ask for input:
How many women are currently in leadership positions? amount_wHow many people (not just women) leave each year? efflux
How many new hires are women? replace_w
Then, I want to make a table where each row is a year. For each year, I want to calculate the amount of women:
2019 > amount of women is the starting amount amount_w
2020 > amount_w = (amount_w[2019] * (1 - efflux)) + ((100 * efflux)*replace_w)
2021 > amount_w = (amount_w[2020] * (1 - efflux)) + ((100 * efflux)*replace_w)Etc.
To give an example:
amount_w =12
efflux = 10
replace_w = 70
Year Calculation Result 2019 12.00000 2020 12 * (1-0.1) + (100*10)*.7 17.80000
I hope this clears things up!To me it looks like an algorithm that calculates N values (based on year). It's a simple formula but the question is where is your data?
You should create a table with a row per year, then another with all possible values of amount_w, then another with all possible values of efflux and another with replace_w
These are your slicers in your page where the person can select their values and will be visible in your formula with a code like amount_w=selectedvalue(TableAmount[amount])
Then your DAX measure it's a little problematic because it's recursive. Amount_w[2020] depends on amount_w[2019], therefore I would use a calculated column rather than a measure.
Finally, don't try porting a python code, iterative, to DAX code which is functional. The logic is completely different, there are no "for" loops. Something that is similar to a for loop are X functions (like SUMX, MINX etc) but, again, they're not for loops.
So my suggestion is to try to learn basics of PowerBI and DAX and then attempt a translation. Unfortunately DAX can't be learned "one step at a time", you need to have strong foundations to make simple use of it.