cancel
Showing results for
Did you mean:
Highlighted
trdoan Member

Calculated column to calculate Average of Sum Quantity per Month

Hi everyone,

Here are my tables:

1. Table "Full Data" which has all Store names and related transaction data:

 Store Name Order Quantity Product Type Posting Date

2. Table "Threshold" has a few Store names (A, E, H) that I want to track and the highlighted columns are the calculated columns I'd like to create:

 Store Name AVG of Monthly Volume Upper Threshold Lower Threshold

Question : The number 80.92 at the bottom of the below picture is what I want to get for the calculated column "AVG of Monthly Volume". Basically calculate the Sum Quantity of each Month then take Average of all of them and show the result in "AVG of Monthly Volume" I want it to be a caculated column because I need it for the below calculated columns "Upper Threshold" & "Lower Threshold":

Upper Threshold =
SWITCH (
TRUE (),
Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 1.2 ,
Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.1 ,
Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 1.2 )

Lower Threshold =
SWITCH (
TRUE (),
Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 0.8 ,
Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.9 ,
Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 0.8 )

Can anyone please advise how I can do that? Thank you so much!

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @trdoan ,

To create a calculated column in tbl_FullData table.

Yearmonth = YEAR('tbl_FullData'[Posting Date])*100+MONTH('tbl_FullData'[Posting Date])

Then update the average column as below.

AVG of Monthly Volume =
CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) )
/ CALCULATE (
DISTINCTCOUNT( tbl_FullData[Yearmonth] ),
FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] ),VALUES(tbl_FullData[Posting Date].[Year]))

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7 Community Support Team

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @trdoan ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member

Re: Calculated column to calculate Average of Sum Quantity per Month

Here is the link to my sample data.

I'd like to create the following 3 calculated columns in the "tbl_Threshold" (though the Upper Threshold & Lower Threshold I already knew how to get them):

 Store AVG Volume Upper Threshold Lower Threshold A ? AVG Volume * 1.1 AVG Volume * 0.9 H ? AVG Volume * 1.05 AVG Volume * 0.95 E ? AVG Volume * 1.2 AVG Volume * 0.8 C ? AVG Volume * 1.15 AVG Volume * 1.15

Taking A as an example using the sample file, it has the following quantity data:

 Store Order Quantity Year Month A 7 2017 July A 65 2017 October A 220 2017 November A 434 2017 December A 57 2018 February A 101 2018 March A 188 2018 April A 103 2018 May A 195 2018 June A 157 2018 July A 169 2018 August A 22 2018 November

So the Calculated Columns should return the following:

 Average Volume Upper Threshold Lower Threshold =(7+65+220+434+57+101+188+103+195+157+169+22)/12 =143.17 =143.17* 1.1 = 157.48 =143.17*0.9 = 128.85

I just don't know how to get Power BI to calculate Net Volume for each month (like what you can see from the first table) then take Average of all those Monthly Net Volumes (like the second table above) to finally produce the 143.17.

Please can you show me how I can return 143.17 in the calculated column 'tbl_Threshold'[Average Volume] ?

Thank you so much! Community Support Team

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @trdoan ,

To use the formula to get the correct AVG of Monthly Volume.

AVG of Monthly Volume = CALCULATE(SUM(tbl_FullData[Order Quantity]))/CALCULATE(DISTINCTCOUNT(tbl_FullData[Posting Date].[Month]))

Then add another two calculated columns.

Upper Threshold =
SWITCH (
TRUE (),
Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 1.2 ,
Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.1 ,
Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 1.2 ,
Threshold[Store] = "C", Threshold[AVG of Monthly Volume] * 1.15)
Lower Threshold =
SWITCH (
TRUE (),
Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 0.8 ,
Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.9 ,
Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 0.8 ,
Threshold[Store] = "C", Threshold[AVG of Monthly Volume] * 1.15
) Pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @v-frfei-msft , thanks for your advice! However, I noticed something strange. The formula only returns correct result for Store A because A accidentally has 12 months worth of data and the DISTINCTCOUNT formula, as I take it, counts distinctively the number of month in the Posting Date column. There are certainly 12 months in a year which makes the formula divide by 12 for every case, even though some Stores might have more or less months.

In the above picture, Store C only has 7 months worth of data and the 686 / 7 would be 98, not 57.17 (which is 686/12). So I doubt the DISTINCTCOUNT will work in this formula.

Is there a way to distinctively count only the months in which the store has data?

Can you please advise ? Thank you! Thank you!!!! Community Support Team

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @trdoan ,

Update the formula as below.

AVG of Monthly Volume =
CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) )
/ CALCULATE (
DISTINCTCOUNT ( tbl_FullData[Posting Date].[Month] ),
FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] )
) Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member

Re: Calculated column to calculate Average of Sum Quantity per Month

Thank you for your help again, the formula still doesn't work out completely. In the above screenshot, there are 11 months for H and this formula only counts 7 distinctive months (June, July, August, September, October, November, and December 2017) and ignores the 4 repeating months (July, August, September, and December 2018).

Can you please help me write another formula that can take both month and year or whatever you see fit to fix this problem? Thanks a lot!!! Community Support Team

Re: Calculated column to calculate Average of Sum Quantity per Month

Hi @trdoan ,

To create a calculated column in tbl_FullData table.

Yearmonth = YEAR('tbl_FullData'[Posting Date])*100+MONTH('tbl_FullData'[Posting Date])

Then update the average column as below.

AVG of Monthly Volume =
CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) )
/ CALCULATE (
DISTINCTCOUNT( tbl_FullData[Yearmonth] ),
FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] ),VALUES(tbl_FullData[Posting Date].[Year]))

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 180 members 1,861 guests
Recent signins:
• ClaireBear • mwegener • henrikj • lravikiran • wasindha • timoz • wenlee • wasindha • kaushik0034 • thiru_c • Keropi79 • nicolekhare • ARUNCHENNAI • almohsen 