Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vtkendra
Frequent Visitor

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.

 

Capture.PNG

 

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.

vtkendra_0-1682020071727.png

 

 

 

 

2 REPLIES 2
grantsamborn
Solution Sage
Solution Sage

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.

Thanks. I added a model and some additional explanation.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors