Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I'm trying to plot my data and identify outliers. Here's my data. I have three industries as categories, and one metric. I want to identify outliers in each industry. I created a Sampling Index column for 'sampling'. I am confused with the settings in the Box and Whisker chart in Power BI. In Chart Options in the tool box, I chose either <1.5 IQR or =1.5IQR as my whisker type, either Exclusive or Inclusive as in the Quartile option, and have the outlier option turned on in all settings. I don't understand the math behind each setting, can someone explain and suggest when to use what? I also attached the graphs that I plot in 4 scenarios.
In my data, negative data would not make sense as the metric does not allow negative numbers. Therefore, I wonder how to look for outliers that are in the lower bound. It looks like the =1.5IQR setting gives me a range that includes negative values, which is not ideal in my case. I wonder if I should use the customize my chart instead. But how?
Please advise when do use =1.5IQR or <1.5IQR, and when to select Inclusive or Exclusive quartile. Thank you!
Industry Name | Metric Name | Sampling Index | Metric Data |
AAA | Metric1 | 1 | 41.4 |
AAA | Metric1 | 2 | 40.6 |
AAA | Metric1 | 3 | 32.4 |
AAA | Metric1 | 4 | 19 |
AAA | Metric1 | 5 | 11.8 |
AAA | Metric1 | 6 | 9.8 |
AAA | Metric1 | 7 | 10.2 |
AAA | Metric1 | 8 | 5.473701539 |
AAA | Metric1 | 9 | 100 |
AAA | Metric1 | 10 | 18.8 |
AAA | Metric1 | 11 | 22.77 |
AAA | Metric1 | 12 | 35 |
AAA | Metric1 | 13 | 9.89 |
AAA | Metric1 | 14 | 6.61 |
AAA | Metric1 | 15 | 9.29 |
AAA | Metric1 | 16 | 11.07 |
BBB | Metric1 | 17 | 14.32 |
BBB | Metric1 | 18 | 1.36 |
BBB | Metric1 | 19 | 1.32 |
BBB | Metric1 | 20 | 0.0127 |
BBB | Metric1 | 21 | 27.81 |
BBB | Metric1 | 22 | 24.3 |
BBB | Metric1 | 23 | 24.47 |
BBB | Metric1 | 24 | 14.24747748 |
BBB | Metric1 | 25 | 13.69800397 |
BBB | Metric1 | 26 | 16.62681592 |
BBB | Metric1 | 27 | 6.294481627 |
BBB | Metric1 | 28 | 5.628550029 |
BBB | Metric1 | 29 | 24.11336717 |
BBB | Metric1 | 30 | 24.36012831 |
BBB | Metric1 | 31 | 0.1099 |
BBB | Metric1 | 32 | 0.13 |
BBB | Metric1 | 33 | 0.06 |
BBB | Metric1 | 34 | 0.06 |
BBB | Metric1 | 35 | 0.05 |
BBB | Metric1 | 36 | 0.05 |
BBB | Metric1 | 37 | 0 |
BBB | Metric1 | 38 | 79 |
BBB | Metric1 | 39 | 78 |
BBB | Metric1 | 40 | 78 |
BBB | Metric1 | 41 | 73 |
BBB | Metric1 | 42 | 73 |
BBB | Metric1 | 43 | 3.62 |
BBB | Metric1 | 44 | 63 |
BBB | Metric1 | 45 | 58 |
BBB | Metric1 | 46 | 14 |
BBB | Metric1 | 47 | 8.5 |
BBB | Metric1 | 48 | 9.3 |
BBB | Metric1 | 49 | 8 |
BBB | Metric1 | 50 | 8 |
BBB | Metric1 | 51 | 8.8 |
BBB | Metric1 | 52 | 7.7 |
BBB | Metric1 | 53 | 5.98 |
BBB | Metric1 | 54 | 38 |
BBB | Metric1 | 55 | 33 |
BBB | Metric1 | 56 | 20 |
BBB | Metric1 | 57 | 50.66 |
BBB | Metric1 | 58 | 51.45 |
BBB | Metric1 | 59 | 50.05 |
BBB | Metric1 | 60 | 51.6 |
CCC | Metric1 | 61 | 21.64 |
CCC | Metric1 | 62 | 14.55 |
CCC | Metric1 | 63 | 0 |
CCC | Metric1 | 64 | 0 |
CCC | Metric1 | 65 | 0 |
CCC | Metric1 | 66 | 0 |
CCC | Metric1 | 67 | 0 |
CCC | Metric1 | 68 | 4.96 |
CCC | Metric1 | 69 | 3.02 |
CCC | Metric1 | 70 | 3.5 |
AAA | Metric1 | 71 | 41.02 |
AAA | Metric1 | 72 | 22.36 |
AAA | Metric1 | 73 | 25.83 |
AAA | Metric1 | 74 | 25.55 |
AAA | Metric1 | 75 | 18.15 |
CCC | Metric1 | 76 | 0.192086903 |
CCC | Metric1 | 77 | 0.244432374 |
CCC | Metric1 | 78 | 0.265173836 |
BBB | Metric1 | 79 | 4.23 |
BBB | Metric1 | 80 | 6.29 |
BBB | Metric1 | 81 | 5.97 |
BBB | Metric1 | 82 | 1.25 |
BBB | Metric1 | 83 | 0.71 |
CCC | Metric1 | 84 | 25.3 |
CCC | Metric1 | 85 | 4.3 |
CCC | Metric1 | 86 | 0 |
CCC | Metric1 | 87 | 0 |
CCC | Metric1 | 88 | 0 |
CCC | Metric1 | 89 | 1.11 |
CCC | Metric1 | 90 | 1.12 |
Solved! Go to Solution.
Hi @wsspglobal ,
You can refer to this article: Power BI Box and Whisker chart.
Quartile calculation:
1. Inclusive - When calculation the 1st and 3rd quartile, the median is included in the calculation. Equivalent of the Excel calculation QUARTILE.INC()
2. Exclusive - When calculation the 1st and 3rd quartile, the median is excluded in the calculation. Equivalent of the Excel calculation QUARTILE.EXC()
Whisker Type:
1. Min/Max - The whiskers represent the minimum and maximum values of the dataset.
2. < 1.5 IQR - The top and bottom whiskers are set to the highest/lowest value of the dataset that are included in the 1.5IQR range.
3. = 1.5IQR - The top and bottom whiskers are set to 1.5IQR of the dataset.
4. Custom - The whiskers can be set to a custom percentile value based on the dataset. The lower value is bound to the lower percentile possible and 25%. And the same is for the higher value, but then from 75% and up to the highest percentile value possible.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @wsspglobal ,
You can refer to this article: Power BI Box and Whisker chart.
Quartile calculation:
1. Inclusive - When calculation the 1st and 3rd quartile, the median is included in the calculation. Equivalent of the Excel calculation QUARTILE.INC()
2. Exclusive - When calculation the 1st and 3rd quartile, the median is excluded in the calculation. Equivalent of the Excel calculation QUARTILE.EXC()
Whisker Type:
1. Min/Max - The whiskers represent the minimum and maximum values of the dataset.
2. < 1.5 IQR - The top and bottom whiskers are set to the highest/lowest value of the dataset that are included in the 1.5IQR range.
3. = 1.5IQR - The top and bottom whiskers are set to 1.5IQR of the dataset.
4. Custom - The whiskers can be set to a custom percentile value based on the dataset. The lower value is bound to the lower percentile possible and 25%. And the same is for the higher value, but then from 75% and up to the highest percentile value possible.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |