Forum Discussion

shephee's avatar
shephee
Frequent Visitor
4 years ago

Multiple criteria with value within a range

Hello.  I am very new to the Power BI community and can see there is a great deal to learn.  I have been working to find a solution for a task I have been working on for several days now.  I have watched numerous videos and have hit several websites but still I am unable to get a solution.  Any advice/support that can be provided would be greatly appreciated.  I hope my illustration below provides you with enough insight into what I am trying to accomplish.

 

Need Help

4 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    shephee,

     

    Welcome to the community! Do you want a calculated column or measure? Here's a calculated column in table fee_breakout:

     

    Total Fee = 
VAR vFee = fee_breakout[Fee]
VAR vFeePct = fee_breakout[Fee %]
VAR vCalcFee =
    ( RELATED ( product_cost[Cost] ) - fee_breakout[Min_cost] ) * vFeePct
VAR vResult = vFee + vCalcFee
RETURN
    vResult

     

    Does the 249,999.99 in the formula refer to Min_cost or Over? I assumed Min_cost, but you can easily change it to Over in the DAX.

     

     

    For future reference, be sure to post sample data in a table format or provide a link to a sample pbix. This will make it easier for people to assist. 🙂

    • shephee's avatar
      shephee
      Frequent Visitor
      4 years ago

      Thank you for your response.  My results return a 1 to many.  All the items in the fee_breakout table instead of just one result.ResultsRelationship

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi shephee 

         

        What is your expected result? As one fee_schedule has multiple rows in the fee_breakout table, if you want to calculate the fee for every row with the related Cost from the product_cost table, you can create a new column with DataInsights 's solution. If that is not what you want, can you please show the expected result based on the sample data? Perhaps you want some aggregation result for every fee_schedule? 

         

        Best Regards,
        Community Support Team _ Jing