- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculated column to calculate Average of Sum ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

trdoan

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2019
07:12 AM

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!

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 8

1 ACCEPTED SOLUTION

Accepted Solutions

v-frfei-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019
06:58 PM

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.

If this post

7 REPLIES 7

v-frfei-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2019
10:34 PM

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.

If this post

trdoan

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019
06:06 PM

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

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!

v-frfei-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019
07:25 PM

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.

If this post

Highlighted
##

trdoan

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019
08:30 AM

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

v-frfei-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019
06:45 PM

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

If this post

trdoan

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-14-2019
02:57 AM

Hi @v-frfei-msft ,

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

v-frfei-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019
06:58 PM

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

If this post