Forum Discussion

KatieH's avatar
KatieH
Advocate IV
9 years ago
Solved

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 the...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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)

     

    Regards,

    Charlie Liao

     

KatieH's avatar
KatieH
Advocate IV
9 years ago

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?

CahabaData's avatar
CahabaData
Memorable Member
9 years ago

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.