Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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]))
Hi,
You should always explain the result. Why is the asnwer against John Doe 2 131?
@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
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.
Sorry, My Bad. It includes Customer 3
the total sale will be for John Doe 2 146
Hi,
You may refer to my solution here.
Hope this helps.
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]))
Why is 4 an opportunity?
Customer 4 is an opportunity, as John Doe 1 not made a sale for it.
OK, great, but what in the data says that? I don't see it.
To solve many-to-many, create a bridge table of unique ID's and relate that table to both of your other tables. Use your bridge table in your slicer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |