• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to create formula in power query to auto follow each level rebate calculate

Oscarr

Member
Hi all


how to create formula in power query to auto follow each level rebate calculate


example
jack is VIP1, he buy 90unit iphone xs total 89,910.00
VIP 1 rebate is 0.25%,if 89,910.00 multiplication 0.25%,he can get 224.78, But there is a limit for cash back which is 150 only for VIP 1 。



pls refer example

Thanks
 

Attachments

  • example.xlsx
    11.2 KB · Views: 9
Personally, I'd just transpose the Max cash back. And load both to PQ and join based on split [Level]. Then it's just matter of multiplication and subtraction on row basis.

See attached sample.
 

Attachments

  • example (5).xlsx
    24.8 KB · Views: 6
Personally, I'd just transpose the Max cash back. And load both to PQ and join based on split [Level]. Then it's just matter of multiplication and subtraction on row basis.

See attached sample.

Hi @Chihiro

it is formula can auto follow same level but different product Rebate?



like ken is VIP 3, buy (huawei p20 pro) and (iphone xs), (huawei p20 pro) Rebate 0.45% and (iphone xs) Rebate 0.30%.
 

Attachments

  • 69E059E9-0B8E-4136-95B3-CAA86A483DF9.jpeg
    69E059E9-0B8E-4136-95B3-CAA86A483DF9.jpeg
    200.8 KB · Views: 7
Sure it does, PQ allows to merge tables on multiple columns, so it will pick the right values for rebate for different product types.
As Chihiro already said, the Max/rebate table should be set-up differently, though PQ can do an un-pivot, pivot thing. Do provide an example in Excel where you'd have multiple products in that table present.
 
Back
Top