Calculations based on slicer selection

[marama]

Hi!

I need to create a report with a gauge visual which shows max, min and average salaries for selected location and position, and then when a user clicks on an employee, the salary of the empoyee is shown in the gague. The problem is, when user selects an employee in the table, the scope is reduced to that selected employee and all the calculations based on slicer selection are based on that single employee only. 

I've tried multiple combination with measures including CALCULATE, AVERAGE, KEEPFILTERS... but I wan't able to achieve the goal so I',m reaching out for help. I'll include the link to the minimalistic pbix file.

https://we.tl/t-HypeZE6zUq

[123abc]

To achieve the desired behavior in Power BI where you can show the max, min, and average salaries for the selected location and position but also display the salary of a selected employee without affecting the overall calculations, you can use a combination of measures and context manipulation. Here's how you can do it:

Step 1: Create Base Measures

First, create base measures for max, min, and average salaries:

Max Salary:

Max Salary = MAX(Employee[Salary])

Min Salary:

Min Salary = MIN(Employee[Salary])

Average Salary:

Average Salary = AVERAGE(Employee[Salary])

Step 2: Create Employee-Specific Measures

Next, create measures that calculate the salary for the selected employee. You can use the SELECTEDVALUE function to get the selected employee's name (assuming the employee's name is unique) and then filter the data accordingly:

Selected Employee Salary:

Selected Employee Salary =
CALCULATE(
MAX(Employee[Salary]),
FILTER(
Employee,
Employee[Employee Name] = SELECTEDVALUE(Employee[Employee Name])
)
)

Step 3: Display All Measures in the Gauge Visual

Now, add all these measures to your gauge visual. You should see the max, min, and average salaries for the selected location and position as well as the selected employee's salary when a specific employee is selected in a table or other visuals.

The key here is to use the CALCULATE function along with FILTER to calculate the selected employee's salary without affecting the overall calculations based on slicer selections. This way, the gauge visual will display the correct salary for the selected employee while still showing the max, min, and average salaries for the broader context of the selected location and position.

Make sure your gauge visual uses these measures appropriately to display the desired information when an employee is selected in another visual.

[marama]

Thank you for helping, but I'm still not there yet ;(

When no employee is selected, it looks OK:

but if I click on Angela, then the scope for all calculations is reduced to Angely only:

Does it have something to do with visual interactions? How do I get max, min and target for gauge visual not being dependant from the table visual, but value to be dependant?

https://we.tl/t-2V97WjZdsM

[123abc]

I understand the issue you're facing. It seems like you want to maintain the context of the slicer selections even when an employee is selected in the table visual. This can be achieved by adjusting the interactions between visuals in Power BI. Here's what you can do:

1. **Review Visual Interactions**:
- Make sure you have reviewed and adjusted the interactions between your visuals. To do this, go to the "Format" tab and select "Edit Interactions" or click the "Format" button in the top menu and then select "Edit Interactions."

2. **Set Visual Interactions**:
- In the "Edit Interactions" mode, you can specify how visuals interact with each other. To achieve the behavior you want, follow these steps:
- Select your table visual (the one with employee names).
- Adjust the interactions for this visual to "None" or "Filter" (experiment with both to see which one works best for your scenario). Setting it to "None" should maintain the slicer context.
- Select your gauge visual (the one showing Max, Min, and Average).
- Make sure the interaction for the gauge visual is set to "Filter" so that it responds to slicer selections.

3. **Test**:
- After making these adjustments, test your report again. When you click on an employee in the table visual, the slicer selections should remain in effect for the gauge visual, and it should show Max, Min, and Average values based on the slicer context rather than just the selected employee.

By adjusting the visual interactions in this way, you can control how visuals respond to user interactions and ensure that the slicer selections remain effective even when you click on an employee in the table.