Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
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
)
)
)
)
)
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
)
)
)
@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
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.
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!
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 !!
Hi, the above example is just for one agent, as there are many and many incentive types, i'm struggling.
by "many incentive type" do you mean Incentive 1, Incentive 2, Incentive 3, etc. ?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |