averagex

18 Topics

Average Monthly Count dynamically with Start Dates
I'm trying to create a Dax Calculation where it gives me the average number of dates entered monthly for the company or for specific employees selected in a slicer. If an employee is selected in the slicer, it must take into account the employees start date, so if a time range is selected before the employee started working, then it will dynamically calculate based on the number of months the employee has been employed. For example, an employee starts work on December 1st 2022 and enters 2 dates in December and 4 dates in January. The company as a whole entered 6 dates in November, 9 dates in December and 12 dates in January. If the enduser selects November through January in the slicer, the company average will be 9 dates entered per month. Then if the enduser selects the employee, it will show 3 dates entered per month because it's not including November which is the month before the employee started. If in February the company and employee both have 0 dates entered, the company calculation would be (6+9+12+0)/4 = 6.75 and the employee calculation would be (2+4+0)/3 = 2. I was able to come up with a calculation that works for last year when an employee is selected, but when an employee is not selected, it still uses the max employee start date for the company. This is also for the entire year and does not work when selecting months in the slicer. Leads Assigned Weekly Average Last Year = IF(YEAR(MAX('SDRs (Accounts)'[Start Date]))=YEAR(TODAY())-1,DIVIDE([Leads Assigned],52-WEEKNUM(MAX('SDRs (Accounts)'[Start Date]))),DIVIDE([Leads Assigned],52)) Leads Assigned =CountA(Lead Date)
TJK
2 years ago Place DAX Commands and Tips
518Views
1like
1Comment
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
Failed to make the formula CALCULATE work
This formula worked for me in another report with no problems. I am trying to recycle it in another Power Bi and I get the error message. "A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression. This is not allowed." I have tried using FILTERS but I couldn't get it to work either. SLA - Communication = AVERAGEX(VALUES(Dates[Biweek Start]), Var StartOfBiweek = CALCULATE(SELECTEDVALUE(Dates[Biweek Start])) Var EndOfBiweek = StartOfBiweek + 13 + 0.999999 RETURN // Open tickets within the biweek: Exclude tickets on Backlog, On Hold or Cancelled at the start of the biweek or during the biweek VAR tbl_open_tickets = VAR Base = CALCULATETABLE(Issues, Issues[Created] < EndOfBiweek, COALESCE(Issues[Closed Date], date(2999,12,31)) >= StartofBiweek) VAR TBL = SELECTCOLUMNS(Base, "Issue", Issues[Key], "Created", Issues[Created], "Closed", COALESCE(Issues[Closed Date], date(2999, 12, 31))) // issues_with_evaluation: Universe of tickets that ticket owner should provide visibility within the next two weeks VAR issues_with_evaluation = ADDCOLUMNS(TBL, "Include 1", //To capture issues that were not in Backlog, On Hold, Cancelled before the start of the biweek, i.e. they were already on these status at the start of the biweek VAR MaxSequence = CALCULATE(max('History Status'[History Sequence]), 'History Status'[History New Value Start] < StartofBiweek) VAR status_at_start_of_biweek = CALCULATE(SELECTEDVALUE('History Status'[History New Value]), 'History Status'[History Sequence] = MaxSequence) RETURN NOT(status_at_start_of_biweek IN {"Backlog", "On Hold", "Canceled","Completed", "Closed"})) RETURN SELECTCOLUMNS(FILTER(issues_with_evaluation, [Include 1] ), "Issues", [Issue]) VAR tickets_with_status_change = CALCULATETABLE(DISTINCT('History Status'[Key]), 'History Status'[History New Value Start] >= StartOfBiweek, 'History Status'[History New Value Start] <= EndOfBiweek ) VAR tickets_with_comments = FILTER(values(Issues[Key]), CALCULATE(COUNTROWS(Comments), Comments[Comment Created] >= StartofBiweek, Comments[Comment Created] <= EndOfBiweek) > 0) VAR open_tickets_count = COUNTROWS(tbl_open_tickets) VAR Tickets_with_comments_or_change = UNION(tickets_with_status_change, tickets_with_comments) VAR count_of_tickets_to_include = COUNTROWS(INTERSECT(tbl_open_tickets, Tickets_with_comments_or_change)) RETURN DIVIDE(count_of_tickets_to_include, open_tickets_count))
AndreaCami
4 years ago Place DAX Commands and Tips
997Views
0likes
6Comments
For Total - get the average of Percentage instead of Sum
Hi, For Total - get the average of Percentage instead of Sum. By default it is calculating sum - I want to get average in of percentage in total - "38%". Can someone please help me with the dax? Thank you in advance
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.7KViews
0likes
1Comment
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
Anonymous
4 years ago Place DAX Commands and Tips
3KViews
0likes
4Comments
Calculate 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
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.4KViews
0likes
1Comment
Average Talk Time Issue
Hi I'm trying to calculate average talk time, and am having issues where the call goes into a new day: My measure is: Duration Average = VAR total_second = AVERAGEX( CallSummary, DATEDIFF(CallSummary[wsp_ucc_AcceptedTime],CallSummary[wsp_ucc_EndTime],SECOND) ) VAR DAXDay = INT(total_second/(24*60*60)) VAR DAXHours = MOD(INT(total_second/(60*60)),24) VAR DAXMin = MOD(INT(total_second/60),60) VAR DAXSec = ROUND(MOD(total_second,60),0) RETURN DAXDay &":"& DAXHours &":"& DAXMin &":"& DAXSec Can anybody see what is wrong with my formula? Thanks!
M_B_
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
4Comments
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!
SwissPowerBI
4 years ago Place DAX Commands and Tips
1.5KViews
0likes
2Comments
Daily Average for each Fiscal Quarter
Hello Community, I am looking to calculate the daily average incidents for each quarter, and I am not quite sure how to go about it. I have calculcated the number of incidents per quarter using DISTINCTCOUNT('Table'[incidents]) : I am doing a YoY quarterly comparison, and my fiscal year starts in October, so Q1 =Oct, Nov, Dec. What I don't know how to do is now divide the number of incidents by the total number of days in the quarter. I have a date table, and one column does contain the number of days in the month, my fiscal quarter is also in the date table. Thank you for the help, Denisse
Solved
Anonymous
5 years ago Place DAX Commands and Tips
1.1KViews
0likes
3Comments