Sum averages with filters

[minhvuong93]

Hello everyone,

I have a dataset of monthly customer invoice by region , like this:

Region _ Customer _Product_OrderQuantity_Month

A         _   Customer 1 _ Product 1__100_____Jan

A       _   Customer 1 _ Product 2__200_____Jan

A       _   Customer 1 _ Product 3__250_____Jan

B      _   Customer 2_ Product 2__200_____Jan

B      _   Customer 2_ Product 2__200_____Jan

.....

A         _   Customer 1 _ Product 1__100____dec

A       _   Customer 1 _ Product 2__200_____dec

A       _   Customer 2 _ Product 4__250_____dec

B      _   Customer 2_ Product 5__200_____dec

B      _   Customer 3_ Product 6__200_____dec

I created a measure to calculate the average total sales volume of full year by Customers , filter by December since I only needed to see current existing customers and their historical sales average.

1. Now I would like to sum all these averages , but Total by each Region
2. How can I calculate the average lines of product each customers bought per 12 month, and the average by Region?(also with the customer list filtered of only DEC)

thanks so much for helping !!!

[Ashish_Mathur]

Hi,

Share a dataset that can be pasted in Excel.  Also, show the expected result there.

[minhvuong93]

@Ashish_Mathur thanks for helping me,

Dataset example here and also the how to caclcualte final output in excel is also explained.

https://1drv.ms/x/s!AiQcjevojvNxhGjXU4Fe29cH1wc6

[Ashish_Mathur]

Hi,

I have computed the "Volume per outlet per month".  By the way, the answer for B should be 304.5 (not 194.5).  You have missed out on B2.  You may download my solution from here.

Please try to solve the Average product bought / month based on this logic.  If you face a problem, post back and i will help you.

[minhvuong93]

@Ashish_Mathur

Hello Ashish,

Thanks so much for helping me.

However about B2, it was left out on purpose.

On Cell V16 in the excel, you can see that I noted: We want to calculate using the list of Outlet Code based on a filtered month (for example, FEB)

This means: B2 already deleted from out active list of outlets in February.

So we only want to subtotal the ACTIVE list of Customers in February

[Ashish_Mathur]

Hi,

I am not clear.  Is the output in range K3:L5 only for the month of Feb or till the month of Feb?

[minhvuong93]

@Ashish_Mathur

The output in K3:L5 :  Total by CustomerCode (A,B,C) is sum from the AVerage sales volume (all months) of the Outlet exist in the month February .

Lets say I filered the month February. 

[Ashish_Mathur]

Hi,

I think i have solved it.  You may download the file from here.

[minhvuong93]

DATESYTD('Calendar'[Date]))

HI @Ashish_Mathur,

Big thanks for your support,

I have a question regarding the Total Sales YTD .

Total sales volume (YTD) = CALCULATE([Total sales volume],DATESYTD('Calendar'[Date]))

I am not sure what the DATESYTD(Calendar[Date]) does but I assume that when a Month in Calendar table is selected, the sales volume is aggregated from the START DATE to END DATE of the month,

With the current file you sent me, I can see that when I select January, the YTD Volume became lower vs Feb. However I still want to see the YTD volume = total of Jan + Feb when getting the AVerage Vol per Outlet

Sorry for not mention this, but what I would like to accomplish is: Total Average Vol per outlet = Sum YTD Average of volume of Existing Outlets in that Month,

Thanks so much,

[Ashish_Mathur]

Hi,

This is thoroughly confusing now.  When you select Jan, YTD will be for Jan only.  Why should Feb be included?

[minhvuong93]

@Ashish_Mathur

Sorry for this confusion,

I want to see the volume to be yearly average to see the full potential sales of the outlets.

Your file work for me though. 

Now it is a matter of the count products bought and the average....

let me see if I can figure this out ,:)

[Ashish_Mathur]

Sure.  Try the count.  If you face a problem post back.

[minhvuong93]

Hi @Ashish_Mathur,

I have uploaded the pbix since it is too heavy to upload data from excel (12 month of approx. 50-60MB excel file per month)

https://1drv.ms/u/s!AiQcjevojvNxhGcIxBDQqcQ1k8N_

[Ashish_Mathur]

Hi,

Please do not share a large file.  Kinldy ensure that the file is only a couple of MB's

[Greg_Deckler]

So, in general, you are going to probably need to use the technique documented here:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The trick is using SUMMARIZE in your measure calculation. Then the question becomes, do you want your total as a separate measure or as the Total line in a matrix or table. If the latter, then see this article:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[minhvuong93]

@Greg_Deckler thanks for helping again,

I think I might have to clarify, my data is like monthly sales orders of each product by outlets from the beginning to end of 2017,

Each outlet belong to a Customer (which is my big Distributors).

So I created two measures after reading your suggestion, but it doesnt get the right outcome.

below is one customer that I filtered to check easier.

Could you look at them and let me know what is wrong?

I used Distinctcount of month column  to get the Average , and filter quantity >0 because if in that month, the customers did not buy anything, then there is still a row of that customers but with the volume ZERO.

AveVPO = CALCULATE((SUM(MK[OrderQuantity])/CALCULATE(DISTINCTCOUNT(MK[Month]),MK[OrderQuantity]>0)),ALLEXCEPT(MK,MK[OutletCode]))

TotalAveVPO = SUMX(SUMMARIZE(MK,MK[CustomerCode],"AVGVPO",[AveVPO]),[AVGVPO])