Hello friends! I need some advice with a project I've been working on.
The basic premise is that I have a table with sales data, an employee table and a date table.
There are several stores(branches) that employees can work at and they rotate frequently. Management wants to see each store sales and performance, as well as individual employee performance.
Dates are in european format "dd.mm.yyyy"
The way I made it work is by taking each employee's start and end date for a specific position and expanding that record for each day.
so for example if John was working as a Junior Sales Rep in Branch 1 from Jan 1-st till Jan 5-th, that record would be expanded into 5 rows with dates from Jan 1 to 5.
Then I create a KEY column by combining the employee ID with the DATE and do the same in the Sales table. So now I can Have a one-to-many relationship between the employee table and the sales table and link each emloyee with their sales for each day.
It works but I feel this is the worst possible way to do it, as it creates bloat and unnecessary data duplication.
What are some industry standarts or pest practices for this kind of setup? My intuition tells me that there should be a way to simply leverage the start and end dates without expanding the employee table for each day...
Any pointers or advice would be much appreciated!
The other problem I have is that I need to calculate a KPI measure for each employee.
Each employee is expected to sell at least 3 units for a total of 400 (for example).
When I create a simple measure it breaks the data model and relationships by duplicating rows for for every employee in the dataset and for each date in the date table, even though there is no data for these dates and employees.
KPI_WRONG = IF(AND([SALES_AMT]>=400,[SALES_COUNT]>=3),"OK","NO")
I was able to find similar topics on this forum and was able to modify my measure to resolve the issue:
KPI =IF (
MAX ( 'Sales'[Sales_Amount] ) <> BLANK(),
Even though I was able to "solve" the issue, I am unable to understand WHY it happens and what exactly causes the rows to duplicate. What am I doing wrong here?
And even though I technically have a working measure, I am still left wondering if this is the way to do it? I feel like my data model is faulty and that is the reason for this strange behaviour. Is there a better way to shape the data?
I attached the example file with dummy data:
Thanks in advance!