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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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