Calculated Threshold By Production and Error

[scoutmedic]

Good afternoon, I'm new to PBI and developing reports for my organization. I've taken some basics courses and searched the forum, but I'm stumped. I'm trying to create a calculated threshold line on a bar chart based on production volume and acceptable error rates so we can see when a location exceeds the acceptable defect threshold. Here is sample data I'm working with:

Production

Cost Ctr	Location	Date	Volume
1	Chicago	1/1/24	300
2	Tokyo	1/1/24	500
3	New York	1/1/24	200
1	Chicago	1/1/24	450
2	Tokyo	1/1/24	550
3	New York	1/1/24	175
1	Chicago	1/1/24	250
2	Tokyo	1/1/24	500
3	New York	1/1/24	300

Errors

Cost Ctr	Date	Category	Part
1	1/1/24	Broken	Wheel
1	1/1/24	Incorrect Part	Gear
2	1/1/24	Missing	Paint
2	1/1/24	Broken	Wheel
3	1/1/24	Broken	Glass
3	1/1/24	Missing	Handle
1	1/2/24	Incorrect Part	Motor
1	1/2/24	Incorrect Part	Gear
2	1/2/24	Broken	Wheel
3	1/2/24	Incorrect Part	Motor

Thresholds

Error	Part	Threshold (per 100 units)
Broken	Glass	0.2
Broken	Motor	0.3
Broken	Wheel	0.1
Incorrect Part	Gear	0.05
Incorrect Part	Motor	0.6
Missing	Handle	0.5
Missing	Paint	0.25
Missing	Wheel	0.3

Any ideas how this can be accomplished?

[lbendlin · Accepted solution]

you will want to expand your data model a bit.

and then you can use TREATAS to lookup the thresholds.

And compare against the actual error rate

[scoutmedic]

That worked great! I was able to create a combo chart and add the Acutal field to the secondary Y-axis to show number vs actual allowed.

It took me a while (and some internet searching) to figure out how not to summarize the Threshold and Actual columns. The trick was creating them as measures, not columns.