Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago
Solved

Poorly calculated average

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, differe...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    @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's avatar
Syndicate_Admin
Administrator
5 years ago

@Aguirre

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

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

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)

datos.png

thank you for the help

A greeting