Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Sum averages with filters

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 !!!


Super User
Super User



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

Ashish Mathur

@Ashish_Mathur thanks for helping me,


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



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.



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



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

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. 





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



Ashish Mathur


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 outletSum YTD Average of volume of Existing Outlets in that Month,



Thanks so much,





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

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 ,:)


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

Ashish Mathur

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)!AiQcjevojvNxhGcIxBDQqcQ1k8N_




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

Ashish Mathur
Super User
Super User

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


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:

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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]))






Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors