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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trdoan
Helper III
Helper III

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

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 others find it more quickly.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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!

 

 

 

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 others find it more quickly.

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

 

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 others find it more quickly.

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

 

 

 

 

 

 

 

 

 

 

 

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 others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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