topic DAX to compute measures based on multiple table and conditions in DAX Commands and Tips

DAX to compute measures based on multiple table and conditions

p_rathinavel — Thu, 12 May 2022 16:17:43 GMT

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!!

Re: DAX to compute measures based on multiple table and conditions

p_rathinavel — Thu, 12 May 2022 16:20:47 GMT

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

Re: DAX to compute measures based on multiple table and conditions

p_rathinavel — Thu, 12 May 2022 16:27:37 GMT

tamerj1 amitchandak Whitewater100 SpartaBI Jihwan_Kim please help!!!

Re: DAX to compute measures based on multiple table and conditions

tamerj1 — Thu, 12 May 2022 16:59:41 GMT

Hi p_rathinavel

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

Re: DAX to compute measures based on multiple table and conditions

Whitewater100 — Thu, 12 May 2022 17:25:22 GMT

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!

Re: DAX to compute measures based on multiple table and conditions

p_rathinavel — Thu, 12 May 2022 19:16:31 GMT

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 !!

Re: DAX to compute measures based on multiple table and conditions

tamerj1 — Fri, 13 May 2022 14:42:55 GMT

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 ) ) )

Re: DAX to compute measures based on multiple table and conditions

tamerj1 — Fri, 13 May 2022 14:55:03 GMT

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 ) ) ) ) )

Re: DAX to compute measures based on multiple table and conditions

p_rathinavel — Fri, 13 May 2022 15:25:54 GMT

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

Re: DAX to compute measures based on multiple table and conditions

tamerj1 — Fri, 13 May 2022 16:08:24 GMT

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.