topic Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI in DAX Commands and Tips

Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Wed, 15 Mar 2023 07:36:39 GMT

Hi there,

I'm having an issue and we are not able to create a measure to calculate the average of these month-to-month data. We are not sure what is happening when we use the average or averagex formula.

What I am trying to calculate is the sum of a column where we have different prices and then I want to calculate the month-to-month average of each data point, but I am not sure what it is calculating for me.

Measures: 17.1 Provider Cost Sum = SUM('17-Volume billing'[Provider Cost]) Average = AVERAGEX('17-Volume billing',[17.1 Provider Cost Sum])

Thanks.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Wed, 15 Mar 2023 08:24:36 GMT

Hi jesusssss

please provide couple of examples

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

Anonymous — Wed, 15 Mar 2023 09:09:29 GMT

Please Provide more info.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Wed, 15 Mar 2023 10:07:00 GMT

Hi tamerj1 and Anonymous

In this Excel file, you can see the unit price data per month and then the average, which is what we would like to replicate in Power BI. The function in the Average cell is in Spanish but it is AVERAGE($B$6:D$6), and we replicated that function in the other cells.

In this image, you can see the column for which we want to calculate the average, which we achieved by creating a custom column that multiplies "quantity" by "net price."

As you can see, the average in the Excel table is not the same as the one I get when I use the AVERAGE function in Power BI, I hope this extra information has been helpful and that we can identify what the problem is. Thank you very much for your help.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Wed, 15 Mar 2023 10:26:02 GMT

jesusssss
Please try

Average = VAR CurrentDia = MAX ( 'Table'[Dia] ) VAR DiaOnAndBefore = FILTER ( ALLSELECTED ( 'Table'[Dia] ), 'Table'[Dia] <= CurrentDia ) RETURN IF ( [17.1 Provider Cost Sum] <> BLANK (), AVERAGEX ( DiaOnAndBefore, [17.1 Provider Cost Sum] ) )

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Wed, 15 Mar 2023 10:43:50 GMT

I just tried it, but I'm still getting the same result as with the average measure. What I think is that it has to do with the calendar[date], meaning that when you put Table[day], I thought you were referring to my date calendar table, but it's still not working. Maybe I have to create an additional column in the calendar table?

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Wed, 15 Mar 2023 11:08:58 GMT

jesusssss
Please try

Average = VAR CurrentDate = MAX ( Calendario[Date] ) VAR DatesOnAndBefore = CALCULATETABLE ( VALUES ( Calendario[Mes] ), Calendario[Date] <= CurrentDate, ALLSELECTED () ) RETURN IF ( [17.1 Provider Cost Sum] <> BLANK (), AVERAGEX ( DatesOnAndBefore, [17.1 Provider Cost Sum] ) )

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 10:49:06 GMT

Hi, I have already tested it and the result is this. They are giving me the same data. I don't know if you know why this could be happening, if it could be an error in the calculation of the supplier cost sum column or in the calendar.

thanks for the help,Let's see if we can figure out what the error is.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Thu, 16 Mar 2023 11:02:34 GMT

jesusssss
Anything else in the filter context other than Month? Also lets avoid using the [Date].[Month] and create the column [Month] or [Year-Month] in the Calendar table.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 11:17:20 GMT

The only filters that exist are the year and month filters.

I just created a column in my calendar table that refers to the months.

But the average is still coming out wrong

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Thu, 16 Mar 2023 11:25:05 GMT

jesusssss
In the table and slicer also use the [monthname] column. Do the same for the [Year]

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 11:55:04 GMT

I have changed the filters on the slicers for the new dates columns of year and month that i have alredy created but it doesn´t work.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Thu, 16 Mar 2023 12:20:47 GMT

jesusssss
What about this?

Please paste the final DAX you're using

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 12:57:13 GMT

You are right, that section was not changed but I changed it and it seems that it takes me new data but it still does not average the excel

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Thu, 16 Mar 2023 13:24:49 GMT

jesusssss
Please try

Average = VAR MaxDate = MAX ( Calendario[Date] ) VAR MinDate = CALCULATE ( MIN ( Calendario[Date] ), ALLSELECTED ( Calendario ) ) VAR DatesOnAndBefore = CALCULATETABLE ( VALUES ( Calendario[Mes] ), Calendario[Date] <= MaxDate, Calendario[Date] >= MinDate, ALLSELECTED () ) RETURN IF ( [17.1 Provider Cost Sum] <> BLANK (), AVERAGEX ( DatesOnAndBefore, [17.1 Provider Cost Sum] ) )

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 14:07:55 GMT

I already tested it and it works correctly, thank you very very much. The only thing is that the month column I created appears to be disordered, I don't know if you know how to fix it.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Thu, 16 Mar 2023 14:15:02 GMT

jesusssss

Create a month number column and use "sort by column " to sort the monthname by monthnunber

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Thu, 16 Mar 2023 14:19:50 GMT

Perfect, it worked correctly. Thank you very much for everything.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

jesusssss — Fri, 17 Mar 2023 12:00:54 GMT

Yesterday, I thought I had managed to sort it out, but apparently not because when I sort it by the 'date' column with the formula I created, it messes up the average again.

This is without sorting.

And this is when I sort it

This is the columns that I have

Calendario = CALENDAR(DATE(2022,1,1),MAXX(Fecha,Fecha[FechaEntregaPrevista]))

monthname = FORMAT(Calendario[Date],"MMMM")

yearname = YEAR(Calendario[Date])

Number of the month = MONTH(Calendario[Date])

I don't know what's going on, whether it's a problem with the calendar table or not. I'm not sure if there is another way to sort the column or to create a 'month' column.

Thanks.

Re: Issue with calculating month-to-month average using AVERAGE or AVERAGEX formula in Power BI

tamerj1 — Tue, 21 Mar 2023 10:50:00 GMT

Hi jesusssss
Sorry I did not notice your reply. You need to use "sort by column" to sort the [monthname] column by the [Number of the month] column