Poorly calculated average

[Syndicate_Admin]

Hello, I am new and it is my first post, I have a problem when calculating an average on the total of a matrix, to explain better I put capture.

I calculate the total sales of 2020 and 2019, difference and variance, so far all correct, I create an average measure of the total and as you can check the data are not correct,

I also show a graph in which the automatic calculation of the average comes out correctly, I guess I'm not doing the measurement well:

Average - AVERAGE(BI_CM[VALUE])

Then when I click the different options and filters that I have, the values vary, but the average is far from what should be its value.

In black it tells me that the average is 103 in 2020

Thanks a lot.

[Syndicate_Admin · Accepted solution]

@Aguirre

The first thing would be to create a calendar table ("New Table" under "Modeling" in the menu):

Calendario =
VAR _MinDate =
    MIN ( BI_CM[FECHA] )
VAR _MaxDate =
    MAX ( BI_CM[FECHA] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _MinDate, _MaxDate ),
        "MesNum", MONTH ( [Date] ),
        "Año", YEAR ( [Date] ),
        "Mes", FORMAT ( [Date], "MMM" )
    )

Once created, sort the "Month" column by the "MesNum" column.

Now create a relationship between the Calendar [Date] and BI_CM[DATE]

Use the fields in the calendar table in visuals, measurements, filters, etc...

For measurements:

Total 2020 = 
      CALCULATE([TOTAL], FILTER(Calendario, Calendario [Año] = 2020))

And for the average:

Promedio 2020 = 
     AVERAGEX(Calendario, [Total 2020])

[Syndicate_Admin]

Perfect. Now if it goes perfectly. Thank you so much for the help.

a greeting.

[Syndicate_Admin]

@Aguirre

Can you show the data model and measurements you're using for totals?

[Syndicate_Admin]

Well, I currently only have one table that's all-inclusive, since I'm starting relatively recently, and I'm testing.

The measure I use to calculate the total for each total year is:

TOTAL = SUM(BI_CM[VALOR])

Total 2020 = CALCULATE([TOTAL], BI_CM[ANNO]=2020)

thank you for the help

A greeting