Dynamic slicer increase percentage

[Brian415_]

I have a dataset in Power BI.

This dataset has data on:

1. Employee Name (Column)
2. Employee Seniority (Column)
3. Employee Current Salary (Column)
4. Employee Salary Increase Minimum Proposal % (Measure)
5. Employee Salary Increase Maximum Proposal % (Measure)

To give you context (fictitious use case):

I want to give my employees a salary increase based on the data.

I have already determined with measures what percentage this increase should be (based on the current salary).I want to create a new measure called: Future Salary.

I would like to create a slicer where the minimum is 'Employee Salary Increase Minimum Proposal %' and the maximum is 'Employee Salary Increase Maximum Proposal %'. This slicer must determine the increase and 'future salary' must display this.

So suppose:

Employee 'John'.
Salary 2023 - $50,000

Employee Salary Increase Minimum Proposal % - 4%

Employee Salary Increase Maximum Proposal % - 10%

I want you to have a slicer between 4%-10% and you can slide the slicer to determine how much increase you want indicates the current salary. So with a 4% increase you should see 'Future Salary' at $52,000.

Do you know the easiest way to achieve this in Power BI? I want a horizontal slicer.

[v-stephen-msft]

Hi @Brian415_ ,

@AmiraBedh 's reply is a good idea. Great!

Slicers with dynamic minimum maximums don't seem to be achievable. But you can create a dynamic percentage slicer and decide whether to return results based on the percentage range of employees.

Then I improved it a bit and attached a .pbix file for reference.

Here's the solution. You can create a what-if paramenters.

When created is clicked, a slicer will be generated.

Then create a measure to claculate the future salary.

Future Salary = var _sel=[Employee Salary Increase Proposal Range Value]
return IF(_sel>=[Employee Salary Increase Minimum Proposal %]&&_sel<=[Employee Salary Increase Maximum  Proposal %],(1+_sel)*SUM('Table'[Employee Current Salary]))

If the percentage in the slicer exceeds an employee's maximum increase percentage, the employee's future salary does not return results.

Hope that helps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[AmiraBedh · Accepted solution]

Create a What-if parameter name it Salary Increase Percentage and set the minimum to 0 and maximum to 1 (or 100 if you want to work in percentage rather than decimal).

You can then create a measure like below :

Future Salary =
VAR SelectedPercentage = SELECTEDVALUE('Salary Increase Percentage'[Value])
VAR CurrentSalary = SUM('Employee'[Employee Current Salary])
RETURN
IF (
NOT ISBLANK(SelectedPercentage) && NOT ISBLANK(CurrentSalary),
CurrentSalary * (1 + SelectedPercentage)
)

and another one to display if the selected percentage is within the employee's allowable range:

Allowable Increase =
VAR SelectedPercentage = SELECTEDVALUE('Salary Increase Percentage'[Value])
VAR MinPercentage = MIN('Employee'[Employee Salary Increase Minimum Proposal %])
VAR MaxPercentage = MAX('Employee'[Employee Salary Increase Maximum Proposal %])
RETURN
IF (
SelectedPercentage >= MinPercentage && SelectedPercentage <= MaxPercentage,
"Yes",
"No"
)

[Brian415_]

@AmiraBedh Thank you so much! Great advice. It exactly works as expected. I indeed gave the slicer a percentage. I gave the slicer the range betwen 0-100. 

I have 2 questions though:

1) I selected the percentage for the slicer. The slicer works well if i move it around. But when I want to add a discount with user input (5%) for example. It doesnt respond very well. It jumps to 100/starts bugging. Do you know how i can fix this? I assume this has to do with the fact that it only accepts a factor or something. My user wants to input numbers like: 5,5 for example

2) Is there a way i can filter the slicer on the percentages of my measures? So if min percentage is 5,5 and max is 15.5. I would like that my slicer is capped between 5,5 and 15,5.

can you maybe help with this? Thanks in advance!

[AmiraBedh]

Just to follow the flow, you may need to accept the answer and then create another question with the detailed required.

Otherwise, here is my answer :

If you're using decimals in your slicer, make sure that the slicer is configured to accept decimal values. If users want to input values like 5.5,
you might need to ensure that the data type and formatting of your what-if parameter is correctly set to handle decimal numbers.

You can't directly bind a slicer to a measure. However, there’s a workaround by creating a table that will hold the possible values for increases,
which will be used to feed the slicer. Here's a strategy you could apply:
Try to create a calculated table that includes all possible percentage increase values. For instance, you could create values from 0% to 100% with 0.1% increments.

Then , relate this table to your main data, ensuring other tables do not directly filter it:

Percentages =
ADDCOLUMNS (
VALUES (GENERATESERIES (0, 100, 0.1)),
"Percentage", [Value] / 100
)

Create a measure that will determine if the selected increase is within the allowable range for each employee :

IsValidIncrease =
VAR SelectedIncrease = SELECTEDVALUE (Percentages[Percentage])
VAR MinIncrease = MIN ('Employee'[Employee Salary Increase Minimum Proposal %])
VAR MaxIncrease = MAX ('Employee'[Employee Salary Increase Maximum Proposal %])
RETURN
IF (
SelectedIncrease >= MinIncrease && SelectedIncrease <= MaxIncrease,
1,
0
)

Please try and tell us 🙂