Compare value in 2 different tables and return result

[mail2vjj]

Hi,

I have a problem for comparing numbers in a column to a limited set of numbers in a different table column and returning a result in a new column.

Please have a look at the tables below, then I will be able to describe my problem in a better way.

First table is the Sales table, which contains the employee ID and the Sales (which is a calculated column).

The second table is the Incentive table, which contains the Incentive slabs and the percentage of incentive.

Now I want to compute the incentive percentage based on the sales.

Sales Table

Employee ID	Sales	Incentive Earned
1	24764
2	77984
3	103161
4	63034
5	154584
6	62805
7	77631
8	122275
9	110846
10	69602

Incentive Table

SLABS UPTO	INCENTIVE %
64999	1.00%
90000	1.25%
115000	2.00%
140000	2.50%
165000	3.00%
195000	3.50%
250000	4.00%
275000	4.50%
275001	5.00%

So for example, for Employee ID Number 2, the Sales is 77,984, so this is greater than 64,999 and less than 90,000( in the Incentive table, 'Slabs upto' column), so 'Incentive Earned' column in the Sales table should return the value 1.00%.

For Employee ID Number 5, the Sales is 154,584, so 'Incentive Earned' column shows 2.50%.

For Employee ID Number 1, the Sales is 24,764, so 'Incentive Earned' column shows 0%....so on and so forth.

Again, the Sales column in the Sales Table is a CALCULATED COLUMN and has more than 500 rows, which need to be compared the 'SLABS UPTO' column.

I have created a relationship between the 'Sales' [Sales] column and the 'Incentive' [Slabs up to] column and tried using the IF function and the RELATED function, which only works if the values are exactly the same, for eg. if sales column has 64999 then result is 1% but if sales is 65000, then it results a blank.

Sorry for posting too long a problem and I will be grateful if anyone can help me with the solution.

Thank you,

[v-huizhn-msft · Accepted solution]

Hi @mail2vjj,
 

I have reproduce your scenario and get expected result. Please see the following solution step by step.

1. Create a calculate column to get the Max Incentive[SLABS UPTO] compared to Sales[Sales].

Column = CALCULATE(MAX(Incentive[SLABS UPTO]),FILTER(Incentive,Incentive[SLABS UPTO]<=Sales[Sales]))

2. Create another new table by clicking "New table" under Modeling on Home page.

Table = ALLSELECTED(Incentive)

3. Create a relationship between 'Table' and Sales table by SLABS UPTO.



4. Create another calculated column to get expected result.

Incentive Earned = IF(ISBLANK(RELATED('Table'[INCENTIVE %])),0,RELATED('Table'[INCENTIVE %]))

Best Regards,
Angelia

[mail2vjj]

Hi @v-huizhn-msft

First of all thank you for your reply, your solution worked and I got the desired result, but I had to tweak your solution a bit.

So now I have a couple of questions, if you would be so kind to please answer them for me.

I will show you what problem I faced and a minor tweak that worked, also I would really appreciate it if you could please explain to me the 2nd formula that you used and how does the function ISBLANK works.

1.

I tried to create a new table used the ALLSELECTED function and it gave me the same table with an extra row. I did check my excel file and there was no blank row in it and probably because of that it gave me an error when I tried to create a relationship between the 'Sales' [Column] and the 'Table' [Slabs Upto]. (For some reason I am unable to insert the screenshot).

So to solve this, I just created a new reference table from the Incentive table and got the realtionship.

2.

I tried using a simple IF function, without creating a new table, so why did that not work, since there is a relationship between the Sales table and the Incentive table.

Incentive Earned = IF ('Sales' [Column] = (Related('Incentive' [Slabs])), RELATED('Incentive'[Incentive %]),0))

......my parenthesis might be wrong.

Why did we have to create a whole new table and create a relationship?

3.

Can you please explain how does your formula work and how does ISBLANK work in this formula?

Incentive Earned = IF(ISBLANK(RELATED('Table'[INCENTIVE %])),0,RELATED('Table'[INCENTIVE %]))

As you might have figured it by now I am fairly new to Power BI and will really appreciate it if you could please help me with this.

Thank you so much for the solution.

[v-huizhn-msft]

Hi @mail2vjj,

1. Please Edit your resource table, and check if there is operation, please see the highlighted in red line.



2. If there is no relationship between two tables, the RELATED function can not be used. If we did not create new table, the relationship can not be created. Becasue the column come from the Incentive table, if create a relationship to Incentive, it's easily to recognize, just like create a relationship by myself.

3. 

Incentive Earned = IF(ISBLANK(RELATED('Table'[INCENTIVE %])),0,RELATED('Table'[INCENTIVE %]))

The ISBLANK function is used to judge if RELATED('Table'[INCENTIVE %] is blank. If it is blank, the logical_test return True, the result is 0. If the logical_test return False, the result is RELATED('Table'[INCENTIVE %].

In addition, please mark the reply as answer if it is helpful. Thanks a lot.

Best Regards,
Angelia

[mail2vjj]

Hi @v-huizhn-msft,

Thank you for your reply, however I am still confused a bit.

For 1.

Yes, I checked my query and it does have the 'Changed type' step in the applied steps section.

The extra row in the new table with 'AllSelected' Function

For 2.

This is what I understood from your reply. We created a new table because without it, putting in the formulas would kind of become like a circular reference.

For 3.

Why are we checking if the Incentive % column is blank or not? The column is not blank so shouldn't it return the value false.

Also, instead of checking the incentive % column, shouldn't we check the new column that we created and use that to get the result in the Incentive earned column?

The new column named 'Column'

Again, thank you for your solution, it works and gives me the desired result, but I am just trying to understand the logic behind it and where did I go wrong in my solution/modeling.

Thanks,

Vishesh