topic Re: Calculating avg based on number of months in DAX Commands and Tips

Calculating avg based on number of months

nidhijo — Sun, 14 Nov 2021 05:40:18 GMT

Hi all!!

I have data that contains quantity ordered and has the dates assosicated with the orders, what I want to calculate is an average of the quantity ordered calculated by number of months. For eg: for the time period june 2019-june 2020 the calculation would be =total number of orders/13 (number of months)

I am doing this to be able to plot an avg line in a line and column chart that shows the quanity ordered by months:

The avg should be indicated by the dark blue line but clearly it is wrong. Also, how can I remove the previous months that have no orders for this particular location, every location has different data...

I also thought of calculating the number of months from the first month ordered till current month, but I'm struggling to do that as well..

Sorry if this is too complicated but any help would be greatly appreciated!!! I'm relatively new to power BI and I've been struggling with this report for the past few days.

Thank you 🙂

Re: Calculating avg based on number of months

Greg_Deckler — Sun, 14 Nov 2021 10:30:05 GMT

nidhijo Can't you just use the Analytics pane's Average feature? Otherwise it is a measure aggregation problem. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Calculating avg based on number of months

nidhijo — Mon, 15 Nov 2021 03:31:44 GMT

I can't use the analytics pane as it is only for the line visual, if I convert this to line visual it does not show me the distrubution for each month which I would like to see. Thanks for the link I'll check out your blog. Also, do you know how I could remove the previous months with no data from my visual? Greg_Deckler

Re: Calculating avg based on number of months

Greg_Deckler — Mon, 15 Nov 2021 11:58:07 GMT

nidhijo To remove the previous months with no data, you need your measures that are returning data in those months to return BLANK instead.

Re: Calculating avg based on number of months

v-chenwuz-msft — Wed, 17 Nov 2021 08:54:08 GMT

Hi nidhijo ,

Depend on you chart, I create some data.

AVARAGE Quantity = VAR _total = SUMX( ALL( 'Table' ), [Orders] ) // If you have condition to calculate the number of quantity order, you can use SUMX(FILTER(ALL('Table'),conditions),[Orders]) VAR _numOfmonth = COUNTROWS( FILTER( SUMMARIZE( ALL( 'Table' ), 'Table'[Date], "sum", SUM( 'Table'[Orders] ) ), [sum] > 0 ) ) RETURN _total / _numOfmonth // If you do not want to display the blank months , try following // IF(ISBLANK([Quantity orders]),BLANK(),_total/_numOfmonth)

The result:

I put my pbix file in the attachment and you can refer, if I misunderstood you, please let me know.

Best Regards

Community Support Team _ chenwu zhu

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