Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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_w
How 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.
>>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
Are you saying I should make a table where I pre-calculate all possible outcomes based on all possible input values? Is there no way to calculate on the fly?
No, i'm saying that if you want amount_w in a filter (so you want the person to be able to "choose a value"), those values (all of them) must be in a column if it's scalar. If it's a float, it can be just a column
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |