cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
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
Helper II

@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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

@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

Super User

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
Helper II

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II
DATESYTD('Calendar'[Date]))

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,

Super User

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
Helper II

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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_

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors