Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
In black it tells me that the average is 103 in 2020
Thanks a lot.
Solved! Go to Solution.
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])
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])
Perfect. Now if it goes perfectly. Thank you so much for the help.
a greeting.
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:
thank you for the help
A greeting
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |