locked
LOOKUP ON MULTIPLE CRITERIA IN DAX RRS feed

  • Question

  • Dear Expert!

    First of all thanks for your contribution in making things possible for learners. 

    I structured a set of tables and made relationships in power pivot to do following things

    1. Make targets for orderbookers brand wise in currency value.
    2. I want to make adjustments (calibration in their monthly targets) with ratio mentioned in table named as TGTCALIB. 

    Note: Targets are made from their previous sales history (which is available in two tables acutalsales1 and actualsales2) by multiplying and increased number let suppose 110% of sales history of last4months, I did it successfully. Butt, since every orderbooker has different circumstances , therefore, I need to calibrate their targets with different %age which is mentioned in TGTCALIB table. 

    Please share formula in DAX for two way lookup value so that i become able to multiply targets to ratios in TGTCALIB table.

    I can share file if there is no compulsion of sharing file link here.

    Saturday, September 9, 2017 11:32 AM

Answers

  • Dilawer,

    First, let me be up front... I am no expert!

    If you have access to YouTube, there are lots of Videos on how to do this.  There are several "set-up" steps, which are required to make this possible.  As I recall, the steps would be something along these lines:

    1. In YouTube, search for "Power BI What If" or "Power BI Scenarios".  I know for a fact "Enterprise DNA" (<cite>https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA) has covered this topic as well as Avi Singh. (PowerPivotPro) https://www.youtube.com/channel/UCRNmSv7mAPYiC0Y40TJijAw.  Search YouTube for and you will get the exact formulas for your measures.</cite>
    2. Next, generally speaking, you will need to do the following: 
    3. Add a table with all the "what-if" scenarios.  In this case, .0 to .5 stepping by .05  (.00, .05, .10, .15 etc.)  Call this table "PCT Goals" or "PCT Targets".  Just a one column table.
    4. Next you would add this as a slicer to your report.
    5. Next, all your measures in your data model subject to this "markup" need to be iterator functions, such as Sales Target = SUMX([Table], Table[Column] * (1 + Selected Markup from Slicer))
    6. You can then use the "Target.." measures in your reports as any other measure.

    Here's a sample search:

    Hope this get's you started...


    John Thomas



    • Edited by jbt_PwrPvt Thursday, March 1, 2018 5:57 PM
    • Proposed as answer by jbt_PwrPvt Wednesday, January 15, 2020 5:21 PM
    • Marked as answer by AV111Editor Monday, May 25, 2020 7:37 AM
    Thursday, March 1, 2018 5:54 PM

All replies

  • Hi Dilawer,

    Thanks for your question.

    It would be better if you can share the sample file and the expected result.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 11, 2017 6:27 AM
  • Hi Dilawer,

    Thanks for your question.

    It would be better if you can share the sample file and the expected result.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Dilawer,

    Do you still need help with this? If so, can you kindly illustrate this scenario with some test data as requested by Willson? Ideally, you'd also provide the expected outputs/outcome based on the test data so that any proposed solutions can be verified against this.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, February 22, 2018 7:58 AM
  • Dilawer,

    First, let me be up front... I am no expert!

    If you have access to YouTube, there are lots of Videos on how to do this.  There are several "set-up" steps, which are required to make this possible.  As I recall, the steps would be something along these lines:

    1. In YouTube, search for "Power BI What If" or "Power BI Scenarios".  I know for a fact "Enterprise DNA" (<cite>https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA) has covered this topic as well as Avi Singh. (PowerPivotPro) https://www.youtube.com/channel/UCRNmSv7mAPYiC0Y40TJijAw.  Search YouTube for and you will get the exact formulas for your measures.</cite>
    2. Next, generally speaking, you will need to do the following: 
    3. Add a table with all the "what-if" scenarios.  In this case, .0 to .5 stepping by .05  (.00, .05, .10, .15 etc.)  Call this table "PCT Goals" or "PCT Targets".  Just a one column table.
    4. Next you would add this as a slicer to your report.
    5. Next, all your measures in your data model subject to this "markup" need to be iterator functions, such as Sales Target = SUMX([Table], Table[Column] * (1 + Selected Markup from Slicer))
    6. You can then use the "Target.." measures in your reports as any other measure.

    Here's a sample search:

    Hope this get's you started...


    John Thomas



    • Edited by jbt_PwrPvt Thursday, March 1, 2018 5:57 PM
    • Proposed as answer by jbt_PwrPvt Wednesday, January 15, 2020 5:21 PM
    • Marked as answer by AV111Editor Monday, May 25, 2020 7:37 AM
    Thursday, March 1, 2018 5:54 PM