Forum Discussion
Helper IIData selection for latest 'available' month
Hello everyone,
I have a dataset of 12 months in 2017.
Customer ID _ Sales volume _ Month
A1 _ 100 _ Jan
A2 - 50 _ Jan
A3 - 120 _ Jan
-.....
A1 _ 120 __ Dec
A2 _30 _ Dec
I would like to filter list of customer remaining in December. (as some customers quit during the year)
Then calculate the average volume sales of the whole year (not only Dec)
How can I accomplish this?
Thanks so much if you can help me, :)
Easiest thing to do would be to create a measure like:
AverageLatest = CALCULATE(AVERAGE(CustomersLatest[Sales]),ALLEXCEPT(CustomersLatest,CustomersLatest[CustomerID]))
Then create a table visualization with CustomerID. Drage Month to your visual filter area and filter to "Dec". Then, drag in your AverageLatest measure.
7 Replies
- Greg_Deckler
Community Champion
Easiest thing to do would be to create a measure like:
AverageLatest = CALCULATE(AVERAGE(CustomersLatest[Sales]),ALLEXCEPT(CustomersLatest,CustomersLatest[CustomerID]))
Then create a table visualization with CustomerID. Drage Month to your visual filter area and filter to "Dec". Then, drag in your AverageLatest measure.
minhvuong93Greg_Deckler thanks for your quick reply,
What is the "CustomerLatest" from?
Does that mean i have to create a new table with only Dec?
- Greg_Deckler
No, that's just the name I gave my table when I recreated your data.
- minhvuong93
Also , could you help me to explain,
what if the customers have the 1st sales in , lets say, OCT and DEC , but no sales in NOV.
So for AVERAGE in this case, will it be: total sales divided by 2 or by 3 month?
- Greg_Deckler
The AVERAGE function is going to take care of all of that and when you put it in a table for each customer, it will be per customer. So, if one customer has 5 entries and another 2 and another 12, AVERAGE will calculate the average correctly, no worries.