Forum Discussion
Resolver IIMany to many relationship - Type 2 data from CSV
Hi All,
Im facing the issue creating an appropiate data model for my 2 report requirements. The 2 CSV files are is like the following
format.
| Sales | |||
| Sales ID | Customer | Sale Amount | Date |
| 1 | 1 | 10 | 3/1/18 |
| 2 | 1 | 120 | 1/1/18 |
| 3 | 2 | 11 | 3/1/18 |
| 4 | 3 | 14 | 3/1/18 |
| 5 | 3 | 15 | 2/1/18
|
| Field Details | ||||
| Customer | Employee | Valid From | Valid to | Valid record |
| 1 | John Doe 1 | 2/16/18 | 12/31/99 | TRUE |
| 2 | John Doe 2 | 2/16/18 | 12/31/99 | TRUE |
| 3 | John Doe 3 | 2/16/18 | 12/31/99 | TRUE |
| 1 | John Doe 2 | 12/31/17 | 2/15/18 | FALSE |
| 3 | John Doe 2 | 12/31/17 | 2/15/18 | FALSE |
| 4 | John Doe 1 | 2/16/18 | 12/31/99 | TRUE |
| 5 | John Doe 3 | 2/16/18 | 12/31/99 | TRUE |
| 6 | John Doe 2 | 2/16/18 | 12/31/99 | TRUE |
I need to create 2 visuals which allows me to following:
1. Total Sale amount made by each Employee (For Example: In case of John Doe 1 this is 10 and John Doe 2 is 146)
2. matrix to show past and future sales including new opportunities for sale per Employee. (For Example: for John Doe 1 - This is should show sales with Sales ID 1,2 as well as opportunity for 4)
Any Help would be greatly appreciated.
Also, Thank you to the community,
I was able to find an answer somewhere else, but anyone is still looking for the answer here,
I created a calculated column "Employee_Sold" and used 2 variables to join and find the answer
Employee_Sold =>
var thisdate = SALES[date] var thisterritory = SALES[customer] return CALCULATE(FIRSTNONBLANK(FieldDetails[Employee],0),FILTER(all(FieldDetails),FieldDetails[Valid from] < thisdate && thisdate <FieldDetails[Valid to] && thisterritory = FieldDetails[customer]))
11 Replies
- Greg_Deckler
Community Champion
Why is 4 an opportunity?
NipponSahoreCustomer 4 is an opportunity, as John Doe 1 not made a sale for it.
- Greg_Deckler
OK, great, but what in the data says that? I don't see it.
- Ashish_Mathur
Super User
Hi,
You should always explain the result. Why is the asnwer against John Doe 2 131?
- NipponSahore
Ashish_Mathur the total for John Doe 2 should be 131 because he has made 2 sales in total. One for Customer 1 for 120 and one for Customer 2 for 11 which totals to 131
- Ashish_Mathur
Hi,
Why should you not consider Customer 3 for John Doe 2? There are 2 dates coresponding to customer 3 in Table 1 - I Feb 2018 and 1 March 2018. 1 Feb 2018 falls in the date range specified in Table 2 for Customer 3.
