Problem with Average Reference Line

DAX DAX Calculations
1

I’ve got the following simple visual, and added a reference line via the Analytics Pane to show the average days per new hire. However, the average associated with line (465 days) is incorrect because it’s just averaging the values of the three bars (510, 482 and 402), rather than accounting for the fact that ARD has multiple hires (which results in the correct average of 476 days per hire, per the table below). I have a measure (AverageDaysAllNewHires) that produces the correct result, but PowerBI doesn’t allow me to choose that measure since it’s not used in the visual. I also tried using a Constant Line, but that only accepts a numeric value, not a measure. Is there any way to correct the average reference line?

Alternatively, it would be fine to include a bar in the visual that represents the average for all divisions, but I couldn’t get that to work either. Any help would be much appreciated. Thanks.

  • Brian

image

2

Honestly I would use DAX here. I don’t believe you can change the way anything in the analytics pane calculates anyway.

I always use DAX myself for anything calculated on a report page, because I know then I have full control over what’s being calculated.

Is there anyway to just change to a visual that allows you to have a line with the bars? Maybe a stacked column charts with lines.

It seems like you have it all there is maybe just about making some slightly different decisions around the visualization of the insight I think.

Thanks
Sam

3

Thanks very much for the quick reply.

I tried some alternative visualizations, but they looked pretty kludgy so I went with your suggested DAX-only approach and incorporated the DAX measure that produces the correct average into a tooltip and a drill-down. Still working out how to incorporate a “total” bar into the horizontal bar chart via DAX.

BTW - really enjoyed today’s video on Difference in Days Between Purchases that you worked out with me a couple of weeks ago. That was the key that unlocked the rest of the project.

Thanks again.

  • Brian