Conditional Approximate Lookups

[adj87]

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 various levels, Entry Level, Professional, Senior, etc.) and the plans they are tallied against those levels:

Sales (Entry Level)	Points
$0	0 pts
$1000	5 pts
$2500	10 pts

Table above is 1 of 4 metrics for 1 of 5 job titles.

I currently have a table in Excel using UDFs full of Case/IFs for all this, but I want to transform that into PowerBI. My first thought was R/Python, but I am not familiar with those languages. My last straw idea is to create tables for each job title, but that almost defeats the purpose of automation since our titles do change/evolve every few years.

Any help/direction would be great, thanks!

[adj87]

Apologies for the abiguity, you always know every facet of your data until you bring in an outside party ... Hopefully this example helps. I've pasted example data/plans here (Forgive me for the endless string of data, I didn't know any other way to send it). The idea being each employee is bonused on Sales/Starts. Those figures are then converted to "Points" and the points factor into the overall payment (among many other things). All these metrics are based on title and in some cases time in the company.

(Plan to follow in second message... this message exceeds 20k characters)

Thank you!

Year	Quarter	Job Title	Sales	Starts
2020	Q1	Entry A	4884	9
2020	Q1	Entry A	1665	0
2020	Q1	Entry A	8099	3
2020	Q1	Entry B	5598	13
2020	Q1	Entry B	4134	16
2020	Q1	Entry B	1794	3
2020	Q1	Professional	6160	3
2020	Q1	Professional	1312	9
2020	Q1	Professional	4860	20
2020	Q1	Senior	8234	6
2020	Q1	Senior	9289	9
2020	Q1	Senior	8006	14
2020	Q2	Entry A	3866	12
2020	Q2	Entry A	2188	4
2020	Q2	Entry A	7751	10
2020	Q2	Entry B	1391	14
2020	Q2	Entry B	3687	7
2020	Q2	Entry B	203	5
2020	Q2	Professional	4877	15
2020	Q2	Professional	9024	6
2020	Q2	Professional	7407	1
2020	Q2	Senior	4319	7
2020	Q2	Senior	5354	20
2020	Q2	Senior	1049	18
2020	Q3	Entry A	7749	12
2020	Q3	Entry A	3281	5
2020	Q3	Entry A	40	2
2020	Q3	Entry B	5397	5
2020	Q3	Entry B	1705	12
2020	Q3	Entry B	7492	16
2020	Q3	Professional	5896	12
2020	Q3	Professional	3949	1
2020	Q3	Professional	4447	17
2020	Q3	Senior	6666	10
2020	Q3	Senior	9	14
2020	Q3	Senior	6730	11
2020	Q4	Entry A	344	17
2020	Q4	Entry A	2057	4
2020	Q4	Entry A	3048	1
2020	Q4	Entry B	1646	5
2020	Q4	Entry B	3961	16
2020	Q4	Entry B	5886	7
2020	Q4	Professional	635	18
2020	Q4	Professional	7783	7
2020	Q4	Professional	5688	7
2020	Q4	Senior	6689	16
2020	Q4	Senior	8201	9
2020	Q4	Senior	3078	6

Entry A - Sales
0	0 pts
1000	5 pts
2500	10 pts
5000	15 pts
Entry B - Sales
0	0 pts
3000	5 pts
5000	10 pts
7500	15 pts
Professional - Sales
0	0 pts
5000	5 pts
7500	10 pts
10000	15 pts
Senior - Sales
0	0 pts
5000	5 pts
7500	10 pts
10000	15 pts
Entry A - Starts
0	0 pts
2.5	5 pts
5	10 pts
7.5	15 pts
Entry B - Starts
0	0 pts
5	5 pts
7.5	10 pts
10	15 pts
Professional - Starts
0	0 pts
5	5 pts
7.5	10 pts
10	15 pts
Senior - Starts
0	0 pts
10	5 pts
15	10 pts
20	15 pts
Entry A / Entry B - Points
0	0
20	Payment
Professional / Senior - Points
0	0
20	Payment
30	Double Payment

[v-kkf-msft · Accepted solution]

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]

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]

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]

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.

[AlexisOlson]

Can you give some example data and the result you're expecting? I'm not really sure what kind of response you're looking for here.

How to Get Your Question Answered Quickly