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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jesusssss
Helper II
Helper II

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

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

jesusssss_0-1678865753923.png

Thanks.

 

1 ACCEPTED SOLUTION

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

View solution in original post

26 REPLIES 26
tamerj1
Super User
Super User

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

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?

jesusssss_0-1678877001748.png

jesusssss_1-1678877022035.png

 

 

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

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.

jesusssss_1-1678963654504.pngjesusssss_2-1678963667440.png

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

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

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

jesusssss_0-1678965363089.png

 

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

jesusssss_1-1678965383767.png

But the average is still coming out wrong

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

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.

jesusssss_0-1678967681548.png

 

@jesusssss 
What about this?

1.png

Please paste the final DAX you're using

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

jesusssss_0-1678971425439.png

 

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

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.

jesusssss_0-1678975610748.png

jesusssss_1-1678975655745.png

 

@jesusssss 

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

 

Hi

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.

jesusssss_0-1679054445083.png

 

And this is when I sort it

 

This is the columns that I have

jesusssss_2-1679054445086.png

 

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.

 

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

hi @tamerj1 

But when I do what you tell me, the data is duplicated in the summation and in the average, unlike putting the average without sorting which appears correctly to me."

Before sort 

jesusssss_0-1679489614814.png

after sort 

jesusssss_1-1679489671085.png

Thanks for the help.

 

@jesusssss 
Double check the Date format in both 'Fecha' and 'Calendario' tables interms of "DD/MM/YYYY" or  "MM/DD/YYYY" 

Hi @tamerj1 

This are all the formats: 

jesusssss_0-1679576640165.pngjesusssss_1-1679576652961.png

jesusssss_2-1679576667377.pngjesusssss_3-1679576678419.png

Its something wrong here?

 

Thanks

 

Hi @jesusssss 
Can we connect?

hi @tamerj1 

Of course, through which platform would you like to connect?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors