You most certainly can use parameters in calculated fields like that.
In the attached workbook I have a parameter that I use to change the value of sales. Have a look and see if that is the basics of what you need.
If you could try explaining what you need to me again, that would be helpful. I wouldn't mind having a crack at this problem, it sounds fun!
Thank you for your input! I looked at your workbook and that is exactly what I want to do. The problem arises when I include more than one parameter in my calculated field.
If I create another parameter and include this in my calculated field like this:
[2012 Sales] * ([Percentage] / 100) + [2013 Sales] * ([Permille] / 1000)
Then the outcome for variable 'Param %age' turns blank in the table (see the attached workbook).
Thank you for taken the time to look at it.
I see what you mean, bringing two parameters together in that way in a calculated field seems to return a blank value. I'm not sure why that is.
However, you can make calculated fields that use the parameters separately and then do additional calculations on the returned values. Like this:
Calc1 = Field * Param1
Calc2 = Field * Param2
EndCalc = Calc1 * Calc2
So by doing this you are essentially writing a calculation like:
(Field * Param1) * (Field * Param2)
Honestly, I'm not 100% sure what your overall requirement is. Could you try explaining it in a different way to me?
The reason your calculated field ([2012 Sales] * ([Percentage] / 100) + [2013 Sales] * ([Permille] / 1000)) didn't work is because you multiplied 2012 sales with 2013 sales! Try changing the 2013 sales to 2012 sales and it will work again.
You could also wrap the 2012 sales and 2013 sales fields in the ZN() function to return 0 instead of nulls.
You are right it works when I use [Sales 2012] in both expressions. This is however not what I am trying to do. (It doesn't work either to use separate calculated fields when [Sales 2012] is used in one of them and [Sales 2013] is used in another.
What I want to create is a dashboard that can show an overall KPI created by 5 different KPIs weighted by a percentage adjustable for the user.
I want a table like this:
ID KPI1 KPI2 KPI3 KPI4 KPI 5 Weigted_ KPI
A 1 2 1 4 1 2.8
The weight that the single KPI should be multiplied with should be adjustable for the user such that if KPI1 is more important in Q1 it is easy for the user to assign a high weight to KPI1 in this quarter in order to get the right overall KPI. Next quarter some other KPI might be more important and it should be easy to adjust the calculation accordingly.
Of course the 5 weights should be visible as a slider like in your workbook.
Hope it makes sence.
I'm having trouble figuring out how this is used in a real example, but I've attempted to give you a solution.
In the workbook attached you can find a sheet with five KPI sliders, ranging from 1-5. There is also a type in KPI Weight % parameter which ranges from 0-1, this is a float to simulate a percentage from 0-100%.
The calculated field "Weighted KPI" calculates the average of the five KPIs and then multiplies this value by the KPI Weight %.
Once again, I'm not exactly sure what your overall intentions are for this but hopefully what I've given you can be a basis for whatever you need.
Thank you for your effort but still not quite what I was looking for. I try to explain it in another way.
We have some key control measures (lets say 2 for simplicity). These key controls should identify the most risky customers. key_control_A > 1 indicates low risk and below 1 indicates high risk. The key controls measure different things so we need to combine them to capture the overall risk of the customers.
Key_control_A is more important than key_control_B so we need to give A a higher weight than B. However I am not sure what the 'right' weights are and what impact the different weights will have on my customer portfolio e.g. how many will get a final key control measure below 1. Therefore I want to create a tool where I can adjust the weights and quickly see the impact on my portfolio.
Therefore the perfect tool would look something like this:
Calculated field: Final_key_control_measure = key_control_A * weight_A + key_control_B * weight_B
where weight_A and weight_B are adjustable parameters.
Hope it makes mre sence now