Finding the maximum value of a measure that combines 3 tables

[jasmin_w]

Hello! I have been trying to get the maximum of a measure for a while now and can't find any solutions to my specific situation. I have 3 tables: Employees, Revenue, and Revenue Quotas. These 3 tables are joined on Employee ID. 

The measures I have already created:

Measure - RevenuePerPerson = 

        SUMX( VALUES('Revenue'[employee_id]),

        CALCULATE(   SUM('Revenue'[Revenue USD]),    'Dates'[Year] = 2023)  )

Measure - QuotaPerPerson = 

    SUMX( VALUES('Revenue Quotas'[employee_id]),

    CALCULATE( SUM('Revenue Quotas'[Revenue Quota USD]),  'Dates'[Year] = 2023) )

Measure - RevenueAchievement =

     DIVIDE ( [RevenuePerPerson], [QuotaPerPerson )

I am looking to display the absolute maximum value of RevenueAchievement and have it be dynamic so it changes when filters are applied.

For example:

	quota per person	revenue per person	revenue achievement	max achievement
employee 1	$100,000	$90,000	90%	90%
employee 2	$100,000	$80,000	80%	90%
employee 3	$100,000	$70,000	70%	90%

I have already tried the formulas listed below. They all returned different values for each row, not one.

  1. Max Achievement = MAXX( VALUES( 'Employees'[employee_number] ), [% Revenue Achievement] )

  2. Max Achievement =

    VAR X =SUMMARIZE(ALLSELECTED(Employees), 'Employees'[employee_id],"M",

                DIVIDE([RevenuePerPerson], [QuotaPerPerson]))

        RETURN MAXX(x,[M])

  3. 

var _table = SUMMARIZE(ALLSelected('Employees'), Employees[employee_id],"Revenue%",[Revenue Achievement])

var _max = MAXX( FILTER( _table, 'Employees'[employee_id] = MAX( Employees[employee_id] )), [Revenue%])

return

CALCULATE(

    MAXX( FILTER( _table, [Revenue%] = _max), [Revenue Achievement] )

)

Please help! Not sure what else to do.

[jasmin_w]

Hi, thank you so much for your solution. It worked perfectly! 

[Alef_Ricardo_ · Accepted solution]

It looks like you're trying to find the maximum value of the "RevenueAchievement" measure and have it dynamically update when filters are applied. The DAX measures you've tried are on the right track, but let's make a few modifications to achieve your goal.

You can create a measure to find the maximum "RevenueAchievement" as follows:

```DAX
Max Achievement =
VAR MaxAchievement = MAXX(ALLSELECTED('Employees'), [RevenueAchievement])
RETURN
IF(ISBLANK(MaxAchievement), BLANK(), MaxAchievement)
```

Here's what this measure does:

1. It uses the `ALLSELECTED` function to consider all filters and selections on the "Employees" table, so it dynamically updates when filters are applied.

2. It calculates the maximum value of the "RevenueAchievement" measure within the filtered context.

3. If there are no valid values (for example, if no employees meet the selected criteria), it returns a blank value to avoid displaying incorrect results.

This measure should give you the maximum "RevenueAchievement" value that updates dynamically based on your filters and selections.

Make sure to replace `'Employees'` with the actual name of your "Employees" table in your Power BI model.

Once you have this measure in your model, you can add it to your table visual alongside other relevant columns, and it should display the maximum achievement value for the selected filters.

[jasmin_w]

Thank you so much! It worked perfectly 🙂