cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Member
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 NameOrder QuantityProduct TypePosting 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 NameAVG of Monthly VolumeUpper ThresholdLower 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"

 

Capture.JPG

 

 

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
Highlighted
Community Support Team
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
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
Member

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

Hi @v-frfei-msft ,

 

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

StoreAVG VolumeUpper ThresholdLower Threshold
A?AVG Volume * 1.1AVG Volume * 0.9
H?AVG Volume * 1.05AVG Volume * 0.95
E?AVG Volume * 1.2AVG Volume * 0.8
C?AVG Volume * 1.15AVG Volume * 1.15

 

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

StoreOrder QuantityYearMonth
A72017July
A652017October
A2202017November
A4342017December
A572018February
A1012018March
A1882018April
A1032018May
A1952018June
A1572018July
A1692018August
A222018November

 

So the Calculated Columns should return the following:

Average VolumeUpper ThresholdLower 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
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
    )

Capture.PNG

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

 

Capture.PNG

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

2.PNG

 

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
Member

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

Hi @v-frfei-msft ,

 

Thank you for your help again, the formula still doesn't work out completely.

Capture.PNG

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

 

 

 

 

 

 

 

 

 

 

 

Highlighted
Community Support Team
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,642 guests
Please welcome our newest community members: