Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Box and Whisker Chart setting: 1.5IQR and Inclusive/Exclusive

 

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 NameSampling IndexMetric Data
AAAMetric1141.4
AAAMetric1240.6
AAAMetric1332.4
AAAMetric1419
AAAMetric1511.8
AAAMetric169.8
AAAMetric1710.2
AAAMetric185.473701539
AAAMetric19100
AAAMetric11018.8
AAAMetric11122.77
AAAMetric11235
AAAMetric1139.89
AAAMetric1146.61
AAAMetric1159.29
AAAMetric11611.07
BBBMetric11714.32
BBBMetric1181.36
BBBMetric1191.32
BBBMetric1200.0127
BBBMetric12127.81
BBBMetric12224.3
BBBMetric12324.47
BBBMetric12414.24747748
BBBMetric12513.69800397
BBBMetric12616.62681592
BBBMetric1276.294481627
BBBMetric1285.628550029
BBBMetric12924.11336717
BBBMetric13024.36012831
BBBMetric1310.1099
BBBMetric1320.13
BBBMetric1330.06
BBBMetric1340.06
BBBMetric1350.05
BBBMetric1360.05
BBBMetric1370
BBBMetric13879
BBBMetric13978
BBBMetric14078
BBBMetric14173
BBBMetric14273
BBBMetric1433.62
BBBMetric14463
BBBMetric14558
BBBMetric14614
BBBMetric1478.5
BBBMetric1489.3
BBBMetric1498
BBBMetric1508
BBBMetric1518.8
BBBMetric1527.7
BBBMetric1535.98
BBBMetric15438
BBBMetric15533
BBBMetric15620
BBBMetric15750.66
BBBMetric15851.45
BBBMetric15950.05
BBBMetric16051.6
CCCMetric16121.64
CCCMetric16214.55
CCCMetric1630
CCCMetric1640
CCCMetric1650
CCCMetric1660
CCCMetric1670
CCCMetric1684.96
CCCMetric1693.02
CCCMetric1703.5
AAAMetric17141.02
AAAMetric17222.36
AAAMetric17325.83
AAAMetric17425.55
AAAMetric17518.15
CCCMetric1760.192086903
CCCMetric1770.244432374
CCCMetric1780.265173836
BBBMetric1794.23
BBBMetric1806.29
BBBMetric1815.97
BBBMetric1821.25
BBBMetric1830.71
CCCMetric18425.3
CCCMetric1854.3
CCCMetric1860
CCCMetric1870
CCCMetric1880
CCCMetric1891.11
CCCMetric1901.12

 

 
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

    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.

1 Reply

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    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.