Box & Whisker Plot (Basic Statistics & Mean Calculation)

Hey learning_dax ,

the mean/average is a beast, not just in Power BI or DAX, but basically everywhere 🙂

Nevertheless, you stumbled on something that you (meaning we) have to be aware of, whenever we throw data to data visualizations. There is a default behavior that we can not change, this behavior is the grouping of rows. Assume we have this simple dataset:

With a given aggregation function of SUM for the column amount:

When we drag the columns to a table visual everything is behaving as expected:

We see the value 4 on the category this.

When we switch the aggregation to mean we see a value of two, the next screenshot shows this (I dragged the numeric column to the visual a 2nd time), it's important to understand that the mean is calculated by SUM( ... ) / NumberOfRows.
Next to that the screenshot also shows the "Box and Whisker by MAQ Software" visual, and now we see the different means:

Basically, this issue can not be resolved due to the default behavior of Power BI: grouping of rows, the number of rows is not available to the visuals.

Most of the time, this is exactly what we want, but from now and then this behavior creates an issue.

You can change this either by changing the default aggregation of the numeric column, from SUM to AVERAGE (of course this is not an option if we are using an explicit measure), this will lead to the same MEAN in the Box and Whisker visual and the table visual, or add a rowidentifier to the dataset:

Using this in combination with the Box and Whisker plot yields the same MEAN as the table visual:

Hopefully, this helps to tackle your challenge and provides some insights on what's going on.

Regards,

Tom