cancel
Showing results 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

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.

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:

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 2
Solution Sage

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.

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

I hope this helps.

Frequent Visitor

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors