Have lived for years on a set of dashboards that have done wonders for me in Tableau. Now trying to recreate in Power BI, where I'm not yet as well-versed. One feature I'm missing is dynamic reference lines. Have read through posts from @TMO_KY and @amitchandak on what looks like a similar challenge, but can't get the solution to work. Similarly good detail from David Eldersveld's DataVeld but still no dice. Wondering what I'm doing wrong.
Have connected to source data on a SQL Server to grab employees, hours logged, dates logged and acknowledgement of holidays. Have added columns to help with interpretation of that data (i.e., adding day of week and a flag to note whether day is a business day). Added a measure to calculate number of hours expected to be logged so long as the day in question wasn't a weekend or a holiday.
Total expected hours showing up in tables and charts should then increase/decrease based on a date slicer. But instead results only show expected hours down at an individual daily level (see table at left in the screen grab). I can't figure out how to aggregate. Results should show 152 hours as the goal per individual in the table at upper right in the screen grab. And they should represent a flat 152 line in the combined bar graph at lower right of the grab.
Would appreciate any insights to get me over this hurdle. Thanks in advance!
@DanMandle For mine I was able to figure it out, so I have 2 charts that I use a static reference line on a dynamic chart. 1 is used for anything over 100% and the other is anything over 80hrs (pay period). I'm not sure if your goal of 152 changes or not but if it doesn't you can always use this measure:
Goal Ref Line = 152
If your goal does dynamically change, try this:
Create a measure:
If these don't help, maybe a PBIX or excel file (without the peoples) names would help to re-create exactly your data.
Thanks for the reply @TMO_KY !
I used to go with the "Goal Ref Line = #" solution in Tableau before I learned about dynamic ref lines on that platform. Those are better given users change the date ranges of these reports, so weekends and holidays are often included in the runs and so need to be accounted for. I tried the TOTAL GOALS measures you suggested but still came out with results that varied employee to employee.
Took your advice and generated an Excel file without the people's names included--going with generic Employee numbers. (Saved to Google Drive.)
Date range in the data set 2/16-2/29/20 should generate an hours goal of 72 hours for each employee--8 hours per day minus weekends minus holidays--in this case Presidents Day on February 17th.
Ideally that number is then pulled in as the value for the Reference Line in the line and stacked column chart (where the columns depict actual hours logged). The Reference Line would be able to dynamically change based on which date range the user has selected in the slicer.
The export includes three columns of data generated after I connected PBI to my SQL data source. In case the problems I'm having are related to how I've calculated those, here are the formulas I used:
I'm hoping this file (and the above formulas) helps to recreate and troubleshoot.
Thanks again for your reply on Friday!
You may be able to simplify this but here is a link to the PBIX file, DanMandle.PBIX - Google Drive
here are some screenshots:
I've also added in a holiday table that can updated based off the year you input in the parameter/function field inside the query. Let me know if this works for you.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!