Creating Dynamic Formulas Based on User Selection

[vtkendra]

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.

[grantsamborn]

Hi @vtkendra 

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1)  Please provide sample data that covers your issue or question completely.  (I hate re-typing sample data.)
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from 1) to 2).

I hope this helps.

[vtkendra]

Thanks. I added a model and some additional explanation.