Filter value from another table

DAX DAX Calculations
1

Hello, everybody,
I need your support.
I have a calculation which shows me the plan fulfilment per employee to a target value. I also have a support table which shows a bonus value per target value. With the following formula the value per employee is not shown. does anyone have an idea?

For Example the correct bonus value for a seller with a performance of 1.39 is 0.013

image
image748Ă—502 21.6 KB 

 Sales People Bonus Rate = 
        var RankingDimension = Values('Staff Total Sales'[Pers.-Nr.])
        Return
        CALCULATE(SUM('Sales People Bonus'[bonus]);
            FILTER(RankingDimension;
                COUNTROWS(
                    FILTER('Sales People Bonus';
                            [Sales People Performance %]='Sales People Bonus'[% Performance to Salesbudget]))>0))

My supporting table looks like

image
image344Ă—682 5.69 KB

This is my data model

image
image823Ă—695 31.3 KB

2

Hi @Mario,

It seems that you are aggregating all values in “Sales People Bonus” table. Can you please try below and see the result? I just replaced SUM with VALUES.

 Sales People Bonus Rate = 

        var RankingDimension = Values('Staff Total Sales'[Pers.-Nr.])
        Return
        CALCULATE(VALUES('Sales People Bonus'[bonus]);
            FILTER(RankingDimension;
                COUNTROWS(
                    FILTER('Sales People Bonus';
                            [Sales People Performance %]='Sales People Bonus'[% Performance to Salesbudget]))>0))
3

I have change the measure and get this error message

image
image1193Ă—739 28.8 KB

4

The solution I need to compare the performance value in the support table and return the corresponding bonus value

image
image1320Ă—769 165 KB

I made a small change in the measure and when I encode the performance value hard it returns the right value.

image
image919Ă—314 11.7 KB

When I insert my measure for comparison it returns an blank value

image

5

Hi @Mario,

Thanks for testing. You are trying hard to find solution yourself which is a great thing. In your 3rd snapshot, you have highlighted measure[Sales People Performance %]. I think you are getting blank value because of context transition in FILTER. I would suggest you to bring that outside filter as a variable and then try. Like assign that measure to variable and use like you used hard coded value.

In case it does not work, I would request you to send sample file. I am sure, it must be very small issue that we are missing.

6

Hi , we’ve noticed that no response has been received from you since the 3rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.

7

Hi @hafizsultan as always, the solution was easier than expected. for comparing performance value and value in the support table. I had to round the performance value to 2 digits after the comma in the measure and the comparison value is found. Thanks for your support.

Performance % Sales People = 
ROUND(
    DIVIDE([Total Sales SalesPeople];[Sales Budget Sales People]);
                                                                2)

BonusRate = CALCULATE(SUM('Sales People Bonus'[bonus]);
                    FILTER('Sales People Bonus';'Sales People Bonus'[% Performance to Salesbudget]=[Performance % Sales People]))
1 Like
8

Hi @Mario,

Thanks for confirmation of issue resolution and I must say it’s a good catch. Indeed, sometimes we are stuck in very minor issue and spend too much time to fix it. However, it’s good for our own learning.