Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
simple, straightforward question. I have the following question:
Customer | Product | Sales | SellDate |
A | A | 300 | 7/10/2018 |
A | A | 400 | 5/10/2018 |
B | A | 200 | 3/10/2018 |
B | A | 300 | 1/10/2018 |
I have calculated an average per customer. For example: Customer A has an average of (300 + 400) / 2 = 350 and B has (200/300) / 2 = 250.
Now I want to show the average linked to the last Selldate:
Customer | Product | AvgSales | Date |
A | A | 350 | 7/10/2018 |
B | A | 250 | 3/10/2018 |
Anyone some smart idea how to make linkage ?
Solved! Go to Solution.
@Anonymous
Please try the coding below. Thanks
average = CALCULATE(SUM('Sheet18'[sales]),FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])))
date = MAXX(FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])),'Sheet18'[selldate])
Proud to be a Super User!
@Anonymous
Hi, this is that you want?
Hi Vvelarde,
thanks a lot for your reply too but that's not exactly what I am looking for.
I need it calculated to perform further actions.
The main goal in the end is to calculate the average of sellings of each customer based on the last selldate and the corresponding quarter.
Customer | Product | Sales | Date |
A | A | 350 | 7/10/2018 |
B | A | 250 | 3/10/2018 |
300 | Q4 2018 |
A dynamic chart will result with the average sellings per quarter when I link the date with my DimDate table.
Therefore I need to calculate the linkage. 😞 Sounds complicated, is complicated.
@Anonymous
I created a new table to get this result. Does this work for you?
table = SUMMARIZE(Sheet18,Sheet18[customer],Sheet18[product],"value",AVERAGE(Sheet18[sales]),"date",MAX('Sheet18'[selldate]))
Proud to be a Super User!
Hi ryan,
that works perfect but would it possible to add it to the current table like that ?
Customer | Product | Sales | Date | Avg Sales | AvgDate |
A | A | 300 | 7/10/2018 | 350 | 7/10/2018 |
A | A | 400 | 5/10/2018 | 350 | 7/10/2018 |
B | A | 200 | 3/10/2018 | 250 | 3/10/2018 |
B | A | 300 | 1/10/2018 | 250 | 3/10/2018 |
@Anonymous
Please try the coding below. Thanks
average = CALCULATE(SUM('Sheet18'[sales]),FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])))
date = MAXX(FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])),'Sheet18'[selldate])
Proud to be a Super User!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |