HELP - Return a value closest to the initial values and displaying an adjacent column.

[Anonymous]

Hello,

I'm struggling to solve this problem.

Below I have two tables and I'm trying to create a column in PBI where it returns the "Grade" base on how close "Base Salary" is to the "Median." The "Median" can be above or below "Base" as long as it the smallest distance (hopefully this makes sense).

Any advice/pointers would be greatly appreciated!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Sow the expected result.  Also, do you want a calculated column or a measure?

[Anonymous · Accepted solution]

Hi @Anonymous 

For your question, here is the method I provided:

Here's some dummy data

“Grade”

"Salary"

You will need to calculate the absolute difference between the 'Base Salary' from one table and 'Median' from the other table. Add a calculated column in the table with the 'Base Salary' to find the absolute difference for each row.

Absolute Difference = 
MINX(
    ADDCOLUMNS(
        'Grade', 
        "Difference", ABS('Grade'[Median] - 'Salary'[Base Salary])
    ), 
    [Difference]
)

Create a calculated column that determines the grade associated with the smallest absolute difference in each base salary.

Closest Grade = 
CALCULATE(
    FIRSTNONBLANK('Grade'[Grade], 1),
    FILTER(
        'Grade',
        ABS('Grade'[Median] - 'Salary'[Base Salary]) = [Absolute Difference]
    )
)

Here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.