Average of Averages
Hi everyone, I'm struggling to handle an easy formula which is a nested average computed along a table with 2 dimension (AUTORE,LINK). Basically I need to compute first of all an average for each author (AUTORE), and then use those aggregated values to create a second level average. AUTORE LINK VALORE autore_1 url_A 20 autore_1 url_B 20 autore_1 url_C 20 autore_1 url_D 20 autore_1 url_E 10 autore_1 url_F 10 autore_2 url_G 20 autore_3 url_H 5 For those familiar with Tableau, you could get the correct result with a nested Level of Deatil I've already checked a similar post (https://community.powerbi.com/t5/Desktop/Average-of-average/m-p/1017894#M481085) but I'm not sure I'm understanding 100%, especially in my case where I have 2 dimension columns and one of them (LINK) is not involved in that caluclus. Can anyone help? Thanks in advance. Fabio
Alternative for AverageX
Hi, I have following working dax code. Since the data volume is high, it runs slow and gives memory error when filtered to lowest level. Is there a better alternative to using @AverageX? The code is: Average Balance (Period) = AVERAGEX( filter ( ALLSELECTED('Date'[Date])|and('Date'[Date]>=min('Date'[Date])|'Date'[Date] <= max ('Date'[Date])))| [Daily Average Balance] ) where, Daily Average Balance = if (ISBLANK([(Cumulative) Opening Balance])| [(Cumulative) Closing Balance]| ([(Cumulative) Closing Balance]+[(Cumulative) Opening Balance])/2 ) Since [Daily Average Balance], [(Cumulative) Closing Balance] & [(Cumulative) Opening Balance] all are measures, I am unable to use Average function. Thanks in advance! Shailee.Measure Subtotals in Matrix not working for both Rows and Columns
hello, I added this Measure in a Matrix, which calculates the transactions volume by hour: Total Transactions = AVERAGE(table1[percentage_by_hour]) * sum(table2[transaction_value]) "table1" has the percentage distribution of transactions by hour, as shown below: hour % channel 1 1% Channel A 2 2% Channel B 3 8% Channel A ... .... .... "table2" has the total volume of transactions for the day, by Channel: day channel transaction_volume feb 1 Channel A 100 feb 2 Channel B 40 ... .... The Matrix has 'Hours' in the columns and 'Channels' in the rows and for some reasons only either the rows subtotal or columns subtotal is showing correctly, but not both together. This is the Measure that I used in the Matrix to show results: Total Transactions NEW = VAR monthlyAverages = AVERAGEX(VALUES(table2[Channel]),[Total Transactions]) VAR AverageTotal = summarize(table2,table2[Channel],"Monthly AVG",monthlyAverages) RETURN if(HASONEVALUE(table2[Channel]), monthlyAverages, sumx(AverageTotal,[Monthly AVG])) This is working by 'Channel', therefore the rows subtotal is correct, but the columns subtotal is wrong. If I replace 'Channel' with 'Hour' the opposite will happen (columns subtotal is correct, and rows subtotal is wrong). How can I make it work so both rows subtotal and columns subtotal show correctly? thanks!Weighted Average Selling Price based on volume and volume remaining
I'm trying to create the DAX to write a funtion for weighted average selling price based on the volume and the volume remaining on a contract. I have lots of dimension filters that can be adjusted. Company Location Contract Customer Item Item Group Contract Type Sales Rep Start Date End Date And then I have my measures: Price Volume Balance Balance % I'm minimize the table to show the important items, but all of my dimensions listed above are columns in the table. Contract Cust Selling Price Weighted Avg Price (Vol) Weighted Avg Price (Bal) Volume Volume Balance Balance % 33033 7445 97.88 2.575789474 0.83484735 535 107 20% 31517 5950 63.66 10.60895622 7.566126744 3388 1491 44% 36366 4544 106.45 1.534178554 2.333499402 293 275 94% 19383 3901 85.32 13.55971077 16.26146831 3231 2391 74% 33943 3138 87.22 12.37297 19.64796493 2884 2826 98% 26441 9029 91.12 11.56367929 15.36946353 2580 2116 82% 26444 3732 108.26 6.75227152 8.319062575 1268 964 76% 29092 4970 131.39 23.61530054 24.87455161 3654 2375 65% 29090 5950 60.11 7.382915396 0 2497 0 0% Total/Average 92.3788889 89.96577177 95.20698 20330 12545 62% Selling Price: Average calculates correctly. = 92.3788889 Total Volume = 20330 Balance = 12545 Balance % = 62% I don't know to calculate the DAX is for these two measures: Weighted Average Price (Vol) = Selling Price * ( Volume / Total Volume ) Example: First Row: WAPV = 97.88 * (535 / 20330 ) = 2.575789474 Aggregated average = 89.96577177 Weighted Average Price (Bal) = Selling Price * ( Balance / Total Balance) Example: First Row: WAPV = 97.88 * (107 / 12545 ) = 0.83484735 Aggregated average bal = 95.20698 Any thoughts? I tried sum and sumx using ALL, ALLSELECTED and ALLEXCEPT, and tried AVERAGEX and the same, but I can't figure it out. Thanks
AverageX on a virtual table
Hello all, I have spent a number of hour researching and not getting anywhere, if there is anybody that can help I would appreciate it! I have a table of dates, which i am building a forecast measure for called 't'. I am trying to add the measure shown in grey on the picture to my table (unsuccesfully) because I cant seem to apply the correct filter to my virtual table. Could anybody suggest code that would allow me to put the figures in grey into my visual using DAX / virtual table only. The code for my workbook (sample data): let Source = Table.FromList(List.Dates(Date.From(DateTime.FixedLocalNow()), 14, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.SelectColumns(Table.AddColumn(Source, "t", each List.Repeat({[Column1]}, Number.RoundUp(Number.RandomBetween(50, 100)))), {"t"}), #"Expanded t" = Table.ExpandListColumn(#"Added Custom", "t"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded t",{{"t", type date}}) in #"Changed Type" My dax calculated measure (Sx ? = where a i would like expected output to be returned: Sx ? = //return the average of all "Sx Ix" by weekday //t[t] = a list of dates VAR _weekday = WEEKDAY(SELECTEDVALUE(t[t]), 2) //the weekday of each t[t] VAR _t_by_date = //a summary of t grouped by t[t] with extra calculated a column called Sx Ix SUMMARIZE( t, t[t], "weekday", WEEKDAY(t[t]), "Sx Ix", DIVIDE( COUNTROWS(t), //a count of all rows grouped by t[t] CALCULATE( COUNTROWS(t), ALL(t), t[t] + 3 >= EARLIER(t[t]) && t[t] - 3 <= EARLIER(t[t]) ) / 7 //a rolling average of 7 days ) //the Sx Ix calculated column ) RETURN "?" /*AVERAGEX( FILTER( SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])), 't by date'[weekday] = _Weekday ), [Sx] )*/ The dax calculated table with calculated column (expected output): t by date = VAR _sx_ix = VAR _weekday = SELECTEDVALUE(t[weekday]) VAR _t_by_date = SUMMARIZE( t, t[t], "weekday", WEEKDAY(t[t]), "Sx Ix", DIVIDE( COUNTROWS(t), CALCULATE( COUNTROWS(t), ALL(t), t[t]+3 >= EARLIER(t[t]) && t[t]-3 <= EARLIER(t[t]) ) / 7 ) ) RETURN ADDCOLUMNS( _t_by_date, "Sx ?", "?" /*"want to add formulated column here" VAR _Weekday = WEEKDAY('t by date'[t]) RETURN AVERAGEX( FILTER( SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])), 't by date'[weekday] = _Weekday ), [Sx] )*/ )Performance Issue with AVERAGEX()
Hi community I have a scenario where I'd like to calculate a % of reached duration. Let's assume I have to Contracts A & B with a given contract duration ("Contract Duration"). The report user is now able to select a date from a slicer ("UserSelection Date"). Depending on this UserSelection Date I'd like to know what percentage of the "Contract Duration" was achieved. Product A: Because the date difference of the "Contract End" and "UserSelection Date" is only 1 day, 99.86% was achieved yet. Product B: Because the "Contract End" is before "UserSelection Date", already 100% were achieved. When there would be a product with a "Contract Start" after "UserSelection Date", the % of duration reached would be 0%. The Total of both products is nearly 100%. When the user changes the "UserSelection Date" to 14.09.2022 or later, the Total is 100%: Now the calculation of the single rows is simple ("% of Duration reached Step 1"--> Date Diff UserSelection / Contract Duration) But the calcuation of the Total differs, because you cannot take the sums of this two values. That's why I'm calculating "% of Duration reached Step 2" with the AVERAGEX of the "% of Duration reached Step 1". This will return the correct value, but is extremly slow. I have only around 2 Million rows and I'm using the import mode - but the calculation of the Step 2 takes around 1 Minute. Additional information: "Product" is just one sample dimension, the formulas should work for other dimensions as well (i.e. vendor). UserSelection Date = MAX(Calendar[Date]) Contract Duration = CALCULATE( SUMX( myTable, DATEDIFF ( myTable[Contract Begin], myTable[Contract End], DAY ) ) ) Date Diff UserSelection = CALCULATE( SUMX( myTable, DATEDIFF ( myTable[Contract Begin], [UserSelection Date], DAY ) ) ) % of Duration reached Step 1 = IF( MAX(myTable[Contract End]) < [UserSelection Date], 1, IF( MAX(myTable[Contract Begin]) > [UserSelection Date], 0, [Date Diff UserSelection] / [Contract Duration] ) ) % of Duration reached Step 2 = AVERAGEX( myTable, [% of Duration reached Step 1] ) Are there other ways to get the correct total value? How can I improve the performance of this calculation? Thank you very much for your support!
Dax Measure to get the average of a column at total level
Hi everybody, Im trying to do a dax measure that has to calculate the total average of the values of the column cells, this measure has to work with any attributes and/or level in the pivot table (excel). The thing is I have correct values only at store level, but no for the other attributes in my model. To better describe my problem, I attached a sample power BI file. thanks a lot for your help! Power BI Sample FileCalculate Average of Percentage in Total
Hi Can someone please help me with the Dax ? Dataset - Calculate - N - Sum of Value where Color is "Red" D - Sum of Value where Color is "Yellow" Div - N/D N = CALCULATE(SUM(Sheet1[Column3]),FILTER(Sheet1, Sheet1[Color] = "Red")) D = CALCULATE(SUM(Sheet1[Column3]),FILTER(Sheet1, Sheet1[Color] <> "Red")) Div = DIVIDE([N],[D]) The highlighted percentage in yellow needs to be the average of above percentages - it should show 89%. Can someone please help me with the DAX? Thanks in advance
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
