DAX to compute measures based on multiple table and conditions

[p_rathinavel]

Hi All,

I have come here believing that the community will be able to help me with a solution.

I’m crashing my head to achieve the below result. I have two tables, the first table with the sales amount made by the agent and the second table is the eligibility criteria for the incentives.

First table

For example, agent 1 has made a sales of 10usd.

Second table

There are different types of incentive calculated, if the agent generates sales amount between 0 and 10 he is eligible for 5%, i.e. 0.5usd

And below is the expected result. i.e. I want to see the agent and their different incentive type and amount.  

Please help, Thanks in advance!!

[tamerj1]

Hi @p_rathinavel 
Correction for the total https://we.tl/t-JtyRxx3uz7

Incentive Amount = 
SUMX ( 
    SUMMARIZE ( TABLE2, TABLE2[AGENT CODE],TABLE2[INCENTIVE TYPE] ),
    CALCULATE ( 
        SUMX (
            TABLE2,
            SUMX (
                TABLE1,
                VAR Amount = TABLE1[AMOUNT]
                VAR MaxSales = TABLE2[MAXIMUM SALES]
                VAR MinSales = TABLE2[MINIMUM SALES]
                VAR Incentive1 = TABLE2[INCINTIVE 1]
                VAR Incentive2 = TABLE2[INCINTIVE 2]
                VAR Incentive3 = TABLE2[INCINTIVE 3]
                RETURN
                    IF ( 
                        Amount >= MinSales && Amount <= MaxSales,
                        Amount * ( Incentive1 + Incentive2 + Incentive3 ) / 100
                    )
            )
        )
    )
)

[tamerj1]

Hi @p_rathinavel 
One simple solution could be using nested iterators (SUMX). I hope the performance won't be an issue.

Here is a sample file https://we.tl/t-m74sXU2n70

Incentive Amount = 
SUMX (
    TABLE1,
    SUMX (
        TABLE2,
        VAR Amount = TABLE1[AMOUNT]
        VAR MaxSales = TABLE2[MAXIMUM SALES]
        VAR MinSales = TABLE2[MINIMUM SALES]
        VAR Incentive1 = TABLE2[INCINTIVE 1]
        VAR Incentive2 = TABLE2[INCINTIVE 2]
        VAR Incentive3 = TABLE2[INCINTIVE 3]
        RETURN
            IF ( 
                Amount >= MinSales && Amount <= MaxSales,
                Amount * ( Incentive1 + Incentive2 + Incentive3 ) / 100
            )
    )
)

[p_rathinavel]

@tamerj1 Thank you very much !! You're the real champion, it worked well for me. 

But the result is slightly inacurate, i.e. agent A has no incentive 1 and incentive 3 but the incentive 3 value is 0 and the final result should be 0. 

And, in the result table it shows 0.15. could you please clarify it for me? thanks

[tamerj1]

@p_rathinavel 

But it has Incentive 2. The code basically sums the the 3 incentives (1, 2 & 3) but the table slices by incentive type. This is how I assumed as I could not think of any other logic. Please advise if I am wrong and let me what is right. 

[Whitewater100]

Hello:

I was wondering if your table two is basically the same as the results you want?

Are you calculating out commisions paid of you just want a list of the commision programs by agent?

Maybe showing some more data on the results table?

Thanks!

[p_rathinavel]

Hi, 

Yes, but the table 2 consists of the rate and not the actual amount. Also, you're right. It's basically the comissions paid of. 

Please find the below images and do the needful 

Thanks !!

[p_rathinavel]

@tamerj1 @amitchandak @Whitewater100 @SpartaBI @Jihwan_Kim please help!!!

[p_rathinavel]

Hi, the above example is just for one agent, as there are many and many incentive types, i'm struggling. 

[tamerj1]

Hi @p_rathinavel 

by "many incentive type" do you mean Incentive 1, Incentive 2, Incentive 3, etc. ?