cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

 

15 REPLIES 15
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 

 

 

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

Thanks so much,

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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_

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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


@ 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!:
Mastering Power BI 2nd Edition

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

 

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

 

 

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors