average of a measure

18 Topics

Perform the equivalent of Excel's SUMPRODUCT on an average * Column , DIVIDED by a other Column
Hello, I have been days trying to solve this issue and I really seem to be at my wit's end, maybe because I'm still too noob. I have this tables and columns of data: Table1: COLUMN_A = Decimal Number COLUMN_B= String, can be two categories, A or B DATEKEY =Date Table2: datetable (with all the usual date table stuff, plus two extra collumns): Usual date table stuff: each row is a day of the year. Date= is the datekey of the datetable, for example 12/02/2020, Year = example, 2020 if datekey is 12/02/2020 Mes = Month of datekey in integer, january = 1, for example, for 12/02/2020 it would be 2. ETC (doesn't matter) Not so usual datetable stuff: -Number_of_days_of_month = Integer, number of days of that month for that row (each row is a day in a year, for example, if datekey is12/02/2020 ;Number_of_days_of_month would have the number 29, as 29 days) -SUMDaynumsofmonth = Integer, agregation of all the total days of all the months that have ocurred up to that point for that row (each row, as said , is a day in a year, so for example if datekey is 12/02/2020, SUMDaynumsofmonth would have the value of 60, because january had 31 days, and february has 29 days, 31 + 29 = 60, this column always gives the sumation of all days of all the months up to that point, NOT the days of the year up to that point, other examples: for datekey 27/04/2020 it would return 31(january) + 29(february) + 31(march) + 30(april) = 121, all rows with datekey of the month of april would return 121 , datekey 29/02/2020 would return 60 again, all the rows in datekey of the month of february would return 60, essentially, it only cares about the month of the datekey, when performing the calculation, not the day. For this column a dax formula was used (that works fine from what I can see, I will share it's calculation nevertheless for completeness of the question) : SUMDaynumsofmonths = //first we calculate the number of days of each month// VAR days_january = DAY(EOMONTH(DATE( YEAR(datetable[Date]),1,1), 0)) VAR days_february = DAY(EOMONTH(DATE( YEAR(datetable[Date]),2,1), 0)) VAR days_march = DAY(EOMONTH(DATE( YEAR(datetable[Date]),3,1), 0)) VAR days_april = DAY(EOMONTH(DATE( YEAR(datetable[Date]),4,1), 0)) VAR days_may = DAY(EOMONTH(DATE( YEAR(datetable[Date]),5,1), 0)) VAR days_june = DAY(EOMONTH(DATE( YEAR(datetable[Date]),6,1), 0)) VAR days_july = DAY(EOMONTH(DATE( YEAR(datetable[Date]),7,1), 0)) VAR days_august = DAY(EOMONTH(DATE( YEAR(datetable[Date]),8,1), 0)) VAR days_september = DAY(EOMONTH(DATE( YEAR(datetable[Date]),9,1), 0)) VAR days_october = DAY(EOMONTH(DATE( YEAR(datetable[Date]),10,1), 0)) VAR days_november = DAY(EOMONTH(DATE( YEAR(datetable[Date]),11,1), 0)) VAR days_december = DAY(EOMONTH(DATE( YEAR(datetable[Date]),12,1), 0)) RETURN //now, depending of the month of the datekey, defined by column mes, we will add the corresponding variables to include the total days of only the months up to the one we are receiving on mes column // IF(datetable[Mes] = 1 ,days_january , IF (datetable[Mes] = 2 , (days_january + days_february) , IF (datetable[Mes] = 3 , (days_january + days_february + days_march) , IF (datetable[Mes] = 4 , (days_january + days_february + days_march + days_april), IF (datetable[Mes] = 5 , (days_january + days_february + days_march + days_april + days_may) , IF (datetable[Mes] = 6 , (days_january + days_february + days_march + days_april + days_may + days_june), IF (datetable[Mes] = 7 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july), IF (datetable[Mes] = 8 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august), IF (datetable[Mes] = 9 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september), IF (datetable[Mes] = 10 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october), IF (datetable[Mes] = 11 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november), IF (datetable[Mes] = 12 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november + days_december)) ) ) ) ) ) ) ) ) ) ) ) I repeat, this column works fine for the moment, the problem comes later Metrics to calculate -metricA = Basically gets the average of COLUMN_A by a specific category , dax formula used: metricA = CALCULATE(AVERAGE(Table1[COLUMN_A ]) , Table1[COLUMN_B] = "A") This metric works fine too, for the moment. -MetricB: Now is where we get to the problem, this metric, should do two things: 1) for each month, calculate metricA (that is essentially, an average with a filter) it should get the average of only that month's data, and afterwards multiply it by the Number_of_days_of_month. It should do this in it's own context for each month. 2) Depending of the month we are working on the graph ( we are creating a matrix visual, that should represent the data like this:) It will add all the metricA of each month up to that point, and then divide them by the corresponding SUMDaynumsofmonth , each month in it's own context. Each number that we see in the row, is basically the SUM(metricA's of all months up to that point) / SUMDaynumsofmonth ( Essentially the sum of all the days that the months up to that point, have). So how did I go about this? MetricB = // Part 1)first we specify that we want metricA, and we calculate it on it's own context as a variable, for each month// VAR presupuesto_a_calcular = [metricA] VAR Pres_january = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1)) VAR Pres_february = ((CALCULATE(presupuesto_a_calcular , datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 2))) VAR Pres_march = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 3) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 3)) VAR Pres_april = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 4) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 4)) VAR Pres_may = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 5) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 5)) VAR Pres_june = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 6) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 6)) VAR Pres_july = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 7) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 7)) VAR Pres_august = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 8 * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 8)) VAR Pres_september = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 9) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 9)) VAR Pres_october = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 10) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 10)) VAR Pres_november = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 11) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 11)) VAR Pres_december = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 12) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 12)) RETURN //Part2) If statements that make it so, that depending of what month we are in, we will add our precalculated variables created earlier, and divide it by the corresponding SUMDaynumsofmonths of that month)// IF(MAX(datetable[Mes]) = 1 ,DIVIDE(Pres_january ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 1)), IF(MAX(datetable[Mes]) = 2 , DIVIDE((Pres_january + Pres_february) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2)), IF(MAX(datetable[Mes]) = 3 , DIVIDE((Pres_january + Pres_february + Pres_march) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 3)) , IF(MAX(datetable[Mes]) = 4 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 4)), IF(MAX(datetable[Mes]) = 5 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april + Pres_may) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 5)), IF(MAX(datetable[Mes]) = 6 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 6)) , IF(MAX(datetable[Mes]) = 7 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 7)), IF(MAX(datetable[Mes]) = 8 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 8)), IF(MAX(datetable[Mes]) = 9 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 9)), IF(MAX(datetable[Mes]) = 10 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 10)), IF(MAX(datetable[Mes]) = 11 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 11)), IF(MAX(datetable[Mes]) = 12 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november + Pres_december) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 12))) ) ) ) ) ) ) ) ) ) ) ) Here is where my code fails, for some reason it doesn't work, returning a smaller division that it should. I Have Applied this process , and it works outside of the big metricB formula, if I do this, it works: januaryexample = CALCULATE(Table1[MetricA], datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1) februaryexample = CALCULATE(Table1[MetricA], datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 2) MetricBforFebruary = DIVIDE(([januaryexample] + [februaryexample]) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2)) When I break it down in three metrics like this the desired result is returned for february in MetricforFebruary, but not so if I do it for all months in MetricB, What is the reason for this?
Solved
Anonymous
2 years ago Place DAX Commands and Tips
2.6KViews
0likes
3Comments
Taking the Average of an Average Measure (Across a Category)
This is useful when you need to take the average of a measure (that is an average itself) across a category of data, for data points that occurred at different dates. (The date part is optional but can be useful when you are working with rolling data) For example, let’s say you have 3 machines, and each machine has a different number of products that it makes, and each product has its own average error rate. The catch is that the machine began manufacturing the different products on different days. And you want to know the average product error rate for each of the 3 machines, 2 quarters after it started manufacturing each of its individual products. Obviously it would be a lot easier if the machine started manufacturing all of its products at the same time, and each machine made the same amount of products, but life gets a little messy sometimes and we just have to roll with it. SO, here we go. Where to start? This is assuming your starting data has a column with machine type, product type, and manufacturing start date, and you also have the error rates organized by products and quarters. You may need to create relationships depending on how your data is set up. Create date measures for the 2 quarters after manufacturing date. Make a new column in your query first, using date.addquarters(column,1), and call it "1 Quarters After Mftg Start." Now, create a simple measure in your report: 1 Qtrs After Mftg Start = min('table'[1 Quarter After Mftg Start]) Do the same thing for 2 Quarters After. Since you probably want a rolling average instead of a snapshot, create a measure that averages the 2 quarters of error rates (this is assuming your error rates are captured in quarters. If not, you can use months, days, etc. for this problem). Avg Error Rate 2 Qtrs After = (CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[1 Quarter After Mftg Star]))+CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[2 Quarters After Mftg Start])))/2 This gives you the 2 quarter rolling average error rate for each product, 2 quarters after its manufacturing start date. Take the average of these product error rate averages, to give you an average error rate 2 quarters after product start manufacturing date for each machine. To do this, you can create a quick measure, average per category. Where the field is the measure you just created [Avg Error Rate 2 Qtrs After] , and the category is "Product." This might seem counterintuitive because you are wanting to group by machine, but if you categorize by machine, the measure is only going to divide by 1 since there is only 1 machine per category. Using "Product" as the category tells the measure to divide by the number of products in the category which will give you an accurate average per machine. Here is what the DAX looks like. Avg Error Rate 2 Quarters After average per Product = AVERAGEX( KEEPFILTERS(VALUES('Table'[Product])), CALCULATE([Avg Error Rate 2 Quarters After])]) ) Now, when you put all this onto a table, make sure to include a "subtotal" line if you want to see the average per machine. When you put this measure onto a bar graph with the machines as your axis, it should show you the average error rate per machine 2 quarters after manufacturing start dates. I hope this was helpful. I have been trying to solve this puzzle for a while now and thought I would share how I cracked it. Let me know if you have any questions!
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.2KViews
1like
1Comment
Filter context and Matrix Visual - Weekly average for selected period
Hi, I am fairly new to power BI and have a problem I cannot solve, Ihave searched the boards and can't find a solution. I would like to display the weekly sales average for a selected period in a matrix visual with the weekending days for the selected period as the columns. I have a calculated table that return a dynamic date period (essentially last 4, 8, 12 weeks) and filters the Date table, which in turn is directly related to the Sales table. So I can offer the user the option to view only the last 4, 8, 12 weeks of Sales. I have a requirement to return a matrix with product code as rows and weekending dates as columns that show total sales, and averages sales for the period selected. I need a measure that retains the time period filters for the average but removes the calendar filters on the visual so that each week column has the same average sales. Current attempt (returns the average for the week column - which is the same as the sales value obviously) is: Currently my Weekly Sales for the Selected Period is Calculated like this: Sales Period Selected = VAR MinWeek = CALCULATE ( MINX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = MIN ( 'Time Period Selector'[Date] ) ), 'Calendar'[WeekEnding] ), ALLSELECTED ( 'Time Period Selector' ) ) VAR MaxWeek = CALCULATE ( MAXX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = MAX ( 'Time Period Selector'[Date] ) ), 'Calendar'[WeekEnding] ), ALLSELECTED ( 'Time Period Selector' ) ) VAR FirstForecastWeek = CALCULATE ( MAXX ( FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[IsFutureDay] = FALSE () && 'Calendar'[IsCompletedWeek] = TRUE () ), ( 'Calendar'[WeekEnding] ) ) ) RETURN CALCULATE ( [Sales Switch], FILTER ( Sales, Sales[Tax date] > MinWeek && Sales[Tax date] <= FirstForecastWeek ) ) And the weekly average is like this: Averagy Weekly Sales = AVERAGEX( SUMMARIZE( ALLSELECTED('Calendar'), 'Calendar'[WeekEnding] ), [Sales Period Selected] ) Which becasue of the Filter Context in the table gives the same value for the week as the sales (average of weekly sales over 1 week = sum of sales) If I remove filters like this AVERAGEX( SUMMARIZE( ALLSELECTED('Calendar'), 'Calendar'[WeekEnding] ), [Sales Period Selected] ), REMOVEFILTERS('Calendar') ) I get the right value (So the correct average over the number of weeks in the selected period) BUT... the martix visual then retunrs ALL the weeks in the calendar table Red weeks NOT in period, Yellow in period Please help
Solved
NanDeb
3 years ago Place DAX Commands and Tips
1.3KViews
0likes
2Comments
Normal Distribution on Aggregated Values using Average for Last 5 Years
Hi All, I am trying to replicate an Exel formula based on Normal Distribution as a Measure in Power BI. In Excel this formula is used on aggregated values of Year and Region. In Power BI I want the formula to work on the unaggregated dataset to avoid having to create and work off a new table. The formula is as follows: NORMDIST(X, avg(last 5 yrs), SD (last 5 yrs) * 1.5) * 100 Where X is the value I want to see where it sits on the distribution. The data (simplified) is as follows: Record No. Category Allocation Approval Year Region 1 A 1 Yes 2020 South 1 B 1 Yes 2020 South 2 C 1 No 2020 North 2 D 1 No 2020 North 3 A 1 No 2022 West 3 D 1 No 2022 West 3 E 1 No 2022 West Records can be assigned multiple categories and these have been pivoted to show one category per row. Because of this the Allocation column is always 1. I would like the equation to be a single measure so I can easily switch between this and the sum of allocation/approval measures I already have. I would want the output to look something like this: Year A B C D E 2020 > 43 56 48 71 28 South 28 56 45 24 58 North 68 15 47 49 57 West 57 58 25 47 85 2021 62 50 48 69 42 2022 56 47 32 58 45 Thanks in advance!
RB_B_ABI_01
3 years ago Place DAX Commands and Tips
559Views
0likes
1Comment
Forecast Accuracy: Calculate the Category average based on percentages subcategories
Hello friends, I'm struggling with this issue for like a month, anybody could help me please? I have two tables: Forecast Table: Account Category Subcategory SubSubcategory Forecasted Value Actual Table: Account Category Subcategory SubSubcategory Actual Value I would like to calculate my forecast accuracy in two methods (like in the simplified table below) : - Weighted Average Accuracy (easy, I got this) - Non Weighted Average of the Subcategory Accuracies (in yellow) After this, I need to be able to filter the results by "Account" with a slicer on my dashboard. This will allow me to understand how well the Account has forcasted for each Category, Subcategory and the third is a KPI that kind of average the two. I think this could be done with some DAX VAR or GROUPBY but didn't manage to do make it right now.. Any ideas folks?
Solved
Anonymous
3 years ago Place Desktop
1.4KViews
0likes
1Comment
Group by + avg
Hello guys, I need to help with following group by and average dax formula my data y_m value 2022_1 1 2022_1 2 2022_1 3 2022_2 4 2022_2 5 2022_2 6 2022_3 7 2022_3 8 2022_3 9 and I would like to get y_m value 2022_1 2 2022_2 5 2022_3 8 I tried to this dax formula measure = GROUPBY(Date_Dim,Date_Dim[y_m],"result",AVERAGEX(CURRENTGROUP(),fact[abs(value)])) but receive an error: The CALCULATE formula cannot be used in expression argument for the GROUPBY() function any idea ? how to do it ?
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
3Comments
Average in subtotal and grand total of a RANKX
Hi all, I have a database with the financial results of several companies by quarters from 2018 to 2021, I have created the following measure in order to give a ranking to each company according to the indicator that is evaluated (for this example "Ventas" and " Activos"): Measure = VAR Ratio = IF( ISBLANK( MAX(Ratios[Valor])), BLANK(), RANKX( FILTER(ALL(Ratios), Ratios[Fecha] = MAX(Ratios[Fecha]) && Ratios[Indicador] = MAX(Ratios[Indicador])), CALCULATE( SUMX(Ratios, Ratios[Valor] + Ratios[NIT] / 1000000000000) ),,DESC,Dense ) ) VAR ColumnaX = ADDCOLUMNS(Ratios,"Rank",Ratio) RETURN IF( HASONEVALUE(Ratios[Fecha]), Ratio, AVERAGEX(ColumnaX,[Rank]) ) Everything was going well until I tried to calculate in the subtotals and grand total the average of the rankings or qualifications that each company has had. For example, in the table below I selected a specific company to check the sub totals and grand totals: As you can see, the formula is not calculating the averages of the rankings for each year and quarter. Do you know if there is a solution for this, in what part of the formula I am falling? Thanks
pfinanciero
4 years ago Place DAX Commands and Tips
1.4KViews
0likes
3Comments
Calculate average, count same group as 1
Hello everyone, In Power BI I need to make the calculations which you can see below. - I want to know the average from column A, B and C seperate. - Each company counts as one. Company x has 4 participants, only 2 filled in a score. Then I calculate 3+4=7 --> 7/2=3.5 - In the case of column A it should be: (3.5+4)/2=3.75. PowerBI calculate the average from all scores and does not count all answers from 1 company as 1. I hope you understand me and can give a solution how I can put this calculations in Power BI. Thanks in advance for you help. Best Regards, Tom
Solved
Anonymous
4 years ago Place DAX Commands and Tips
3.6KViews
0likes
9Comments
Measure - how to sum value over a week (for players) and then average this based on category
Hi guys, first time posting so please forgive me if I'm not very clear! Dummy data and issue shown below. Date WK Start Date Player Category Value 28/09/2021 25/09/2021 A 1 2000 28/09/2021 25/09/2021 B 1 5000 27/09/2021 25/09/2021 A 1 3000 27/09/2021 25/09/2021 B 1 4000 So first I want a measure to SUM the week (based on week start date) e.g. expected result for Player A is a sum of 5000 for week beginning on the 25th. Then I want a measure for the Category AVERAGE for that week e.g. expected result is players Bs sum being 9000 for the same week, so the average for Category 1 would be 7000. The full data set will include multiple rows of data over many dates and week start dates, and each player will have an assigned category. Any ideas on what the Dax measures (not columns) would look like for these measures? Thanks in advance! Logan
Solved
LS-PScience
4 years ago Place DAX Commands and Tips
4.6KViews
0likes
3Comments
Need Help - Average a new measure
Hi All, I am trying to average a new measure in a card but struggling. The card is only reading a total amount and giving no option to go to an average. Attached are some photos. Any help is much appreciated.
Anonymous
4 years ago Place DAX Commands and Tips
1.5KViews
0likes
7Comments