Forum Discussion

BlueTiger's avatar
BlueTiger
Icon for Helper I rankHelper I
6 years ago

selecting the correct target for staff while keeping relationships

I have a number of Bullet Chart visuals which have worked great for the first financial year. They show staff names with sum of value of their work completed against a personal monthly target.

 

We have just entered a new financial year and the manager has entered new rows of targets for each staff in a spreadsheet, which has: Name, FY, Monthly Target. i.e. John, 2020, 25000.

 

The problem I have now that my relationship is broken as staff are named twice in this sheet, I used to have a 1:1 relationship between the staff in this spreadsheet and the main staff table. If I set it to 1:* then targets get summed on the visuals. I need it to show targets for the financial year of the data it is displaying.

 

Solutions I can think of:

1. Get rid of FY column and only allow current targets, no historical. (In source spreadsheet or power query)

2. Create a new table of only the current financial year targets and use that for the 1:1 relationship.

 

Neither of these seem the right solution. Although we aren't currently reporting on historical targets, I'm sure at some point we will want to compare this year to last year. I'd like the implement the best solution for the data model going forward and I am aware I can't be the first person to encounter what seems like a very common issue. I am sure I'm missing the correct solution.

 

Can anyone help with what is their experience or best approach for the data model when dealing with targets?

Need Help

3 Replies

  • Wimverh's avatar
    Wimverh
    Icon for Resolver IV rankResolver IV
    6 years ago

    Hi BlueTiger, 

     

    It's hard to tell without seeing the entire datamodel. 

    Anyhow you have to make a filter on the Financial Year. Even better would be to change this column to a financial date, 

    and then link this field to a calender table. 

    Then you have the option to use functionality like previousyear()

     

    Regards,

    Wim

    • BlueTiger's avatar
      BlueTiger
      Icon for Helper I rankHelper I
      6 years ago

      That would be the ideal solution. I actually tried this previously, but the problem is trying to have more than one active relationship to the targets table. I already have the staff name as an active relationship which links to the "sales" table. If I tried to link the financial year to the date table that became an inactive relationship, so I can't use it for filtering.

       

      I agree on changing Year to a date would be better (what if they decide to change targets mid year at some point?).

      The good thing is I have complete control to change the data structure and model as I see fit, but I am trying to keep it simple too rather than create a mess of a report that nobody can adjust in the future without breaking something.

       

      I'll try to create an example report this morning to demonstrate, although it is very basic, I know it helps to see it visually. I am sure I'm missing something simple.