Forum Discussion
Helper VFind Outliers by Dimension
We paint FIVE different widgets (A, B, C, D and E).
- They are painted on one of several MACHINES.
- They are painted on different DATES.
- They are painted different COLORS.
When a widget is painted, it is inspected and given a quality SCORE between 0 (good) and 5 (bad). Anything below 2 is passing. The challenge is to find bad scores, and see if they belong to one or more dimensions (machine/date/color). Some scenarios with root causes:
- Outliers might be linked to a particular machine, but not a color or day (machine not operating correctly)
- Outliers might be linked to a particular color and day, but not a machine (bad supply of paint used on a particular day)
- Outliers might be linked to a particular color and day and machine (can of paint loaded into a machine incorrectly)
What is the best way to display this information in Power BI and what is the best tool to use? (DAX? PBI Insights?)
The mock-up below isn't necessarily what I want; it's just to demonstrate that someone will want to look at each widget type and see where the problem areas are. It might be a report with drill-thru to underlying data. Some sample data is included (showing an ongoing problem with BLUE paint):
create table #widget (widget_id int, widget_type varchar(1), machine varchar(8), color varchar(8), paint_date date, quality_score tinyint)
insert into #widget values
(14, 'B', 'Beta', 'Green', '2020-08-01', 0),
(15, 'B', 'Alpha', 'Blue', '2020-08-01', 3),
(16, 'B', 'Omega', 'Blue', '2020-08-05', 5),
(17, 'B', 'Alpha', 'Green', '2020-08-02', 1),
(18, 'B', 'Alpha', 'Red', '2020-08-03', 0),
(19, 'B', 'Beta', 'Blue', '2020-08-03', 4)
(Try not to make too much sense of the scenario. It's a generalization. For example, a bad batch of red would obviously affect all widget types.)
1 Reply
- Greg_Deckler
Community Champion
bvy - Not sure if there is enough information to go as how you should go about this is going to be heavily dependent on your actual setup. Bubble charts can be useful in this situation because they support putting a lot of information in a single chart. The built-in AI visuals could prove useful. Machine learning in general might be the way to go if you have a large number of dimensions.
Seems like you could create a measure like COUNTROWS(FILTER('Table',[Score] > 2). You could use that in all kinds of different visuals until you find a pattern or an outlier. Quick insights might be another way to go. You might even be able to do something linear regression and correlation coefficients. Again, there are a ton of options but I am not clear on what exactly you are going for, how automated you want things to be, how often circumstances change, etc.
Also, seems like a failure rate would be a key KPI: https://sciencing.com/calculate-failure-rates-6403358.html#:~:text=To%20calculate%20the%20failure%20rate%2C%20divide%20the%20number,is%20so%20small%20that%20it%20is%20almost%20insignificant.
