Targets and rewords based multiple levels

[Ritaf]

Hi All , i am trying to create some dashboard for Suplliers rebates calculation. Every supplier is selling a products from multiple manufacturers. Part of them give us an rebate based on minumum of buying from one of his manifacturers. Ihave an rebate targets-rewards table by supplier and manifacturer and transactions table . How can i calculate the reward on supplier's level based on the minimum of supplier+manifacturer? I attached a pict of table with example i hope i explained my self clearly.

Thanks,

Rita

[amitchandak]

@Ritaf ,Can you share sample data and sample output in table format?

[Ritaf]

Thank you for response

I put the file here https://drive.google.com/file/d/1uGDWIyGpLTVSTMTz6tCV6h2Oa1duP_X1/view?usp=sharing

[amitchandak · Accepted solution]

@Ritaf , Try a measure like

sumx(summarize(Transaction, Supplier[Supplier],Manufacturer[Manufacturer], "_1", sum(Transaction[purchase sum]), "_T1",max(Target[Target1]), "_T2",max(Target[Target2])
, "_R1",max(Target[reward1]), "_R2",max(Target[reward2])), switch(true(), not(isblank(_T2)) && [_1] >=[_T2] , [_1] *[_R2],
[_1] >=[_T1] , [_1] *[_R1] ,0))

[Ritaf]

Thanks a lot! It worked