1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Power Pivot, Power Map etc' started by Oscarr, Jan 28, 2019.

  1. Oscarr

    Oscarr Member

    Messages:
    82
    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

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    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.

    Attached Files:

    Oscarr likes this.
  3. Oscarr

    Oscarr Member

    Messages:
    82
    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%.

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Really depends on how you set up your tables.
    Oscarr likes this.
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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.
  6. Oscarr

    Oscarr Member

    Messages:
    82
    OK Thanks...i try first

Share This Page