Forum Discussion
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
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
TomMartens this explanation is exactly what I was inquiring about and it makes sense as to why it behaves that way. The "row identifier" solution will work, but do you mind sharing the formula for the measure or calculated column that may work?
I have tried a couple different methods but again duplicate values pose another challenge when I index columns when we need all unique row identifiers. Thanks for all of your help.
Hey learning_dax ,
there is not DAX solution, as the number of rows that will be passed to the visuals will not change. What I was trying to say was this, if you pass a measure to the visual, then you have to use a row identifier. If you use a numeric column then you can maybe overcome the issue by changing the default aggregation function to AVERAGE.
Regards,Tom
