Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Dynamic Reference to Sums

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!


Annotation 2020-08-07 145300.png

Helper I
Helper I

@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:

Total Goals = CALCULATE(SUM('Table'[Hrs Goal]))
Total Goals = CALCULATE(SUM('Table'[Hrs Goal]) * COUNT('Table'[Name]))
I tried to recreate your information as best as I could and it worked for me, in the first measure, it comes up with agoal of 32 (per person) where as the second measure takes that per person goal and multiplies it by the # of people to give me an overall goal of 128.  In which this creates a static line for one or the other. 

If these don't help, maybe a PBIX or excel file (without the peoples) names would help to re-create exactly your data.

Frequent Visitor

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:

  • Day of Week Name = FORMAT('Table'[Date],"dddd")
  • isWeekDay = IF((('Table'[Day of Week Name]="Saturday") || ('Table'[Day of Week Name]="Sunday")),"No","Yes")
  • HoursGoalforBizDayNOTHoliday = IF(('Table'[isHoliday]=True)||(('Table'[Day of Week Name]="Saturday") || ('Table'[Day of Week Name]="Sunday")),"0","8")

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.



Frequent Visitor

Looking forward to giving that PBIX file a go @TMO_KY ! 
Have requested access to the Drive. 

Helpful resources

August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors