Forum Discussion

DanMandle's avatar
DanMandle
Frequent Visitor
6 years ago

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 referenc...
Need Help
TMO_KY's avatar
TMO_KY
Helper II
6 years ago

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's avatar
    DanMandle
    Frequent Visitor
    6 years ago

    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!

    • TMO_KY's avatar
      TMO_KY
      Helper II
      6 years ago

      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:

       

       

      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's avatar
        DanMandle
        Frequent Visitor
        6 years ago

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