Conditional Approximate Lookups

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.