Forum Discussion

adj87's avatar
adj87
Helper I
4 years ago
Solved

Conditional Approximate Lookups

Hi Everyone, I could really use some help here. I have lots of experience with Excel but I am relatively new to PowerBI.   I have a series of performance data (sales figures for employees at vario...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi adj87 ,

     

    I think you need to create the following tables in Power BI.

    • Job - A table containing all the job names.
    • FactTable - A table containing sales and starts data.
    • Points - A table with rules for converting sales and starts to points.
    • Payment - A table with rules for converting points to payment.

     

        

      

     

    Then add the "End" columns to table Points and table Payment, indicating that the sales/starts/points data between the Values Start/Points Start and the Values End/Points End can be converted to the corresponding points/payment.

     

     

    If you import the table from Excel file in the following form, then you need to do some conversions to transform it to table Points and table Payment. Please refer to the attachment for the steps and code.

     

     

    Then create the following relationships and measures.

     

    SumSales = SUM(FactTable[Sales])
    Sales_Points = 
CALCULATE (
    MAX ( Points[Points] ),
    FILTER (
        Points,
        Points[Category] = "Sales"
            && Points[Values Start] <= [SumSales]
            && Points[Values End] > [SumSales]
    )
)
    Starts_Points = 
CALCULATE (
    MAX ( Points[Points] ),
    FILTER (
        Points,
        Points[Category] = "Starts"
            && Points[Values Start] <= [SumSales]
            && Points[Values End] > [SumSales]
    )
)
    TotalPoints = value( SUBSTITUTE( [Sales_Points], "pts", "" ) ) + value( SUBSTITUTE( [Starts_Points], "pts", "" ) )
    Payment = 
CALCULATE (
    MAX ( Payment[Payment] ),
    FILTER (
        Payment,
        Payment[Points End] > [TotalPoints]
            && Payment[Points Start] <= [TotalPoints]
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi adj87 ,

 

I think you need to create the following tables in Power BI.

  • Job - A table containing all the job names.
  • FactTable - A table containing sales and starts data.
  • Points - A table with rules for converting sales and starts to points.
  • Payment - A table with rules for converting points to payment.

 

    

  

 

Then add the "End" columns to table Points and table Payment, indicating that the sales/starts/points data between the Values Start/Points Start and the Values End/Points End can be converted to the corresponding points/payment.

 

 

If you import the table from Excel file in the following form, then you need to do some conversions to transform it to table Points and table Payment. Please refer to the attachment for the steps and code.

 

 

Then create the following relationships and measures.

 

SumSales = SUM(FactTable[Sales])
Sales_Points = 
CALCULATE (
    MAX ( Points[Points] ),
    FILTER (
        Points,
        Points[Category] = "Sales"
            && Points[Values Start] <= [SumSales]
            && Points[Values End] > [SumSales]
    )
)
Starts_Points = 
CALCULATE (
    MAX ( Points[Points] ),
    FILTER (
        Points,
        Points[Category] = "Starts"
            && Points[Values Start] <= [SumSales]
            && Points[Values End] > [SumSales]
    )
)
TotalPoints = value( SUBSTITUTE( [Sales_Points], "pts", "" ) ) + value( SUBSTITUTE( [Starts_Points], "pts", "" ) )
Payment = 
CALCULATE (
    MAX ( Payment[Payment] ),
    FILTER (
        Payment,
        Payment[Points End] > [TotalPoints]
            && Payment[Points Start] <= [TotalPoints]
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

adj87's avatar
adj87
Helper I
4 years ago

Thank you! This is a great help. I've applied these examples to my main workbook.

 

Your attached/measures above for Starts_Points references "SumSales" so I added another measure "SumStarts". I adjust the attached model with this change, and everyone calculated correctly.

 

I did notice a odd error. I fat fingered 1 of the point tables so that a Professional Recruiter would get 3 pts at the 0-11 and 0-8 ranges depending on group.

 

 

This error caused everyone, regardless of title, to recieve at least 3 pts (I've corrected it above). Based on my limited understanding of the formulas used here, I didn't think that would occur. I assume this has something to do with the relationship linking Job Title/rest of the tables and the "order of operations" for lack of a better term (measure applied first then the relationship for example). Any light shed on this would be helpful so I can find a way to better avoid errors like this.

Thank you!

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi adj87 ,

     

    This is because the measure [Starts_Points] does not filter the Group column, which means that in the existing model, if the Starts value is 9, then the "FILTER" function will filter out the rows: "0  11  0" and "8  10  3". Then it will calculate the maximum value of the Points column from these two rows, which is 3.

     


    So if your FactTable and Points table both contain the Group columns, you need to add this inactive relationship to the model.

     

     

    Then modify the measure.

     

    Starts_Points = 
CALCULATE (
    MAX ( Points[Points] ),
    FILTER (
        Points,
        Points[Category] = "Starts"
            && Points[Values Start] <= [SumStarts]
            && Points[Values End] > [SumStarts]
    ),
    USERELATIONSHIP ( FactTable[Group], Points[Group] )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • adj87's avatar
      adj87
      Helper I
      4 years ago

      That did it, thank you! I thought I could get away with adding additional relationships just like with Job Title table, so I had this initially. Forgive me, Payment/LS_Payment are not well named, they should be:

      Payment -> Payment_A

      LS_Payment -> Payment_B

       

       

      Now I have this, the FactTable[Group],Points[Group] relationship is a Many/Many, but doing an random sampling, the data does seem to calculate correctly, so I don't know if this is necessarily a bad thing in this situation.