Forum Discussion

vtkendra's avatar
vtkendra
Frequent Visitor
3 years ago

Creating Dynamic Formulas Based on User Selection

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.

 

 

 

 

2 Replies