Creating Dynamic Formulas Based on User Selection

vtkendra · ‎04-20-2023

I am not very proficient writing DAX so if someone can help me, I would truly appreciate it. I have the data as shown below.

The table has a measure called Comp/RVU = sum(compensation) / sum(rvus)

The report has filter so users can filter by a specific person [Name] and quarter.

I want to ADD a report filter so the user can choose to either compare the individual's comp/RVU to the company average comp/RVU OR their site's comp/RVU. Then, I would have another measure called:

Variance = ( [Company or Site Comp/RVU] - [Individual Person's Comp/RVU] ) / [Company or Site Comp/RVU]

The field [Company or Site Comp/RVU] should be dynamic based on whether the person selects that they want to compare their data to the Site they work at or the entire company average.

EDITED:

Here is sample data:

https://drive.google.com/file/d/1hVfUfTpPgXvoNDMczdcJqkkitssBoL7W/view?usp=share_link

I want to have a filter for the person so that you can select which person you want to look at on the report.

I also want to have a slicer where the user can select "Compare to Comp/RVU for the Site" OR "Compare to Comp/RVU for the Company".

Based on the slicer selection, there would be a formula calculating the variance.

For example, if someone selects Cathy and "Compare to Site", then the variance would be calculated between Cathy's measure $500 and the Site average for Boston which is $479

However, if the user selected Cathy and "Compare to Company", then the variance would be calculate between Cathy's measure $500 and the COMPANY average which is $458.