cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KatieH
Advocate IV
Advocate IV

Daily Targets

Hi all, hoping to get some help and guidance around adding target info to my Dashboard.

 

I have report that shows all calls made by a Department in the Company (Call Department).

This data is then plugged into a report showing gauges for each employee summing up their number of calls and set against a daily target of 25 and a maximum daily value of 40.

 

Up til now, this has been fine as I just have a table linked to the data with the list of employees and those 2 values next to each name.

 

Now I need to be able to change the gauge values based on weekly/monthly time periods (along with setting a 0 target for weekends). 

 

What is the best way for me to add this data in? The only way I can think of at the moment is to add a new table showing each employee against all dates with a target for each day:

 

EmployeeDateDayTargetMax Value
Bob01/09/2017Friday2540
Bob02/09/2017Saturday00
Bob03/09/2017Sunday00
Bob04/09/2017Monday2540
Fred01/09/2017Friday2540
Fred02/09/2017Saturday00
Fred03/09/2017Sunday00
Fred04/09/2017Monday2540

 

But this seems like a massive undertaking - is there an easier way that I'm not aware of??

 

All help gratefully received!

 

Katie.

1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft
Microsoft

@KatieH,

 

It's not very hard to create such a table. I have tested it on my local environemnt, the steps below are for you reference.

 

  1. Create a employee table.
  2. Create a date table.
    Date = CALENDAR(DATE(2017,9,1),DATE(2017,9,30))
  3. Cross join those two tables.
    Table = CROSSJOIN('Date',Employee)
  4. Create two column in new table.
    Target = IF(WEEKDAY('Table'[Date])>=2&&WEEKDAY('Table'[Date])<=6,25,0)
    MaxValue = IF(WEEKDAY('Table'[Date])>=2&&WEEKDAY('Table'[Date])<=6,40,0)

Capture.PNG

 

Regards,

Charlie Liao

 

View solution in original post

4 REPLIES 4
v-caliao-msft
Microsoft
Microsoft

@KatieH,

 

It's not very hard to create such a table. I have tested it on my local environemnt, the steps below are for you reference.

 

  1. Create a employee table.
  2. Create a date table.
    Date = CALENDAR(DATE(2017,9,1),DATE(2017,9,30))
  3. Cross join those two tables.
    Table = CROSSJOIN('Date',Employee)
  4. Create two column in new table.
    Target = IF(WEEKDAY('Table'[Date])>=2&&WEEKDAY('Table'[Date])<=6,25,0)
    MaxValue = IF(WEEKDAY('Table'[Date])>=2&&WEEKDAY('Table'[Date])<=6,40,0)

Capture.PNG

 

Regards,

Charlie Liao

 

CahabaData
Memorable Member
Memorable Member

well you have to define that weekly/monthly factor (assuming it is a multiplier against 25/40 ) or value if you want to have absolutes.

 

you'll need to set up a unique table; day of week,day of month, month, factor (or values fields)

 

am assuming those columns are the key determinations for the factor (or values) to vary....  that new table should be good forever......

 

then you'll need to apply a compare of the date in question - maybe your dates table - to grab that factor/values

www.CahabaData.com

Thank you so much for your response Cahaba. Unfortunately you are dealing with a complete newbie here. I'm not sure how to implement your solution.

 

I have tried to create a unique table with the day of the week and the targets linked to that but, when I use those fields in the gauges, it simply sums everything.

 

The targets need to change in line with the number of days I'm selecting. The value figure already does but, if I select 2 days for example, the target stays at 25 when it should move to 50.

 

Does that make sense?

Well let's say you create a 'Target Table'

Monday  25  40

Tuesday  26  41

etc

 

In your original post you display a fact table that has also the day of week; Mon, Tues, etc

 

You can therefore join your new Target Table to your fact table on that field - - and so those values are now available in your visuals.

 

It gets more complicated if the Target Table is both month and day of week dependent - as then you can't do a simple join between tables

 

January Monday  25 40

January Tuesday  26  41

 

but must instead calculate/compare the date of the fact table with your Target Table.  It is not newbie stuff - but if you craft your Target Table and display a few sample records the community can help you get there.

 

my approach is that it should be table driven; possibly it can all be formula driven - in which case you would need to specifiy the formula that works in all cases.

 

www.CahabaData.com

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors