cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanMandle
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

4 REPLIES 4
TMO_KY
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]))
 
or
 
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.

 
DanMandle
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!

@DanMandle 

 

You may be able to simplify this but here is a link to the PBIX file, DanMandle.PBIX - Google Drive 

 

here are some screenshots:

TMO_KY_0-1597067220043.png

 

TMO_KY_1-1597067247523.png

 

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.

 

 

DanMandle
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors