Issue with rolling average based off item type
Hello, I'm looking to create a rolling 90 day/3 month average of LeadTime over the course of a year. This is what my data looks like The issue with this is I have three different WorkItemType values: "Task", "Feature" and "PBI" so even though all the data is in a single table, I need three different rolling averages, one per type. The DAX I have so far is for a single type, Feature. I assume I will need 3 measurements in total, one for each. Rolling Average = VAR LastTransactionDate = MAX('Query1'[CompletedDateSK]) VAR AverageDay = 90 VAR PeriodInVisual = FILTER( ALL( 'Query1'[CompletedDateSK] ), AND( 'Query1'[CompletedDateSK] > LastTransactionDate - AverageDay, 'Query1'[CompletedDateSK] <= LastTransactionDate ) ) VAR WorkItemType = FILTER( ALL( 'Query1'[WorkItemType] ), AND( 'Query1'[WorkItemType] = "Feature", 'Query1'[WorkItemType] = "Feature" ) ) VAR OutPut = CALCULATE( AVERAGEX( 'Query1', [LeadTimeDays] ), PeriodInVisual, WorkItemType ) RETURN OutPut Which feels like it can be improved. I'm also not sure it is calculating the right values either, since the rolling average is being calculcated as the same as the month average How can I calculate rolling average correctly given my data? Thanks!
AVERAGE CUSTOMERS PER WEEK
I HAVE A TROUBLE I NEED THE AVERAGE CUSTOMER'S OF ANY YEAR-NUMBER WEEK (1 TO 52), THE PERFORMANCE IT'S THAT ONLY TAKE THE DIFFERENTE CUSTOMER'S IN THE LAP ACUM, EXAMPLE: IF JIM MORTENSEN (CUSTOMER) APPEAR EN EVERY WEEK, THE DAX ONLY TAKE LIKE A 1 CUSTOMER EN X PERIOD, I NEED THAT HE COUNT SEVERAL TIMES THAT THE WEEKS HE APPEAR'S. CAN ANYBODY HELPME PLEASE.
Self-reference with DAX for Average
Hi Everyone, I have an Issue currently I have a measure called [Rate] which will be provided for actual calculated months, what I need to avhive in the [Average] Column is if the [Rate] has a value keep the rate otherwise provide an average of the last 12 months but e.g. in the table provided Nov will be the average of Nov 23 to Nov 24 [Rates] but for December I will need to take the recently calculated rate for Nov 24 and consider it for the Avg Dec 23 to Dec 24 my main issue is power BI is not self referencing the latest calculated row inted is only taking the moving average until Nov 24. Here is the Dax I'm using: DEFINE var _RollingPeriodStart = DATE(2024,3,1) var _Start = DATE(2023,1,1) var _PreviousMonth = DATE(2025,5,31) var _LastSync = [Latest Sync Date] var _Table = SUMMARIZE( FILTER( ALL('Calendar'), 'Calendar'[Date] >= _Start && 'Calendar'[Date] <= _PreviousMonth ), 'Calendar'[Year-Month], 'Calendar'[Year Month Class], "EoMDate", LASTDATE('Calendar'[Date]), "Rate", [_Rate] * 100 ) var _Table_2 = ADDCOLUMNS( _Table, "Index", RANKX( _Table, 'Calendar'[Year Month Class], , ASC, DENSE ) ) var _Table_3 = ADDCOLUMNS( _Table_2, "Average", IF( NOT(ISBLANK([Rate])), [Rate], AVERAGEX( FILTER( _Table_2, [Index] < EARLIER([Index]) && [Index] >= (EARLIER([Index]) - 12)), [Rate]))) EVALUATE _Table_3 This what need to be achieved. Instead Power BI is doing this
Create an Average calculation grouping by two different fields
I need to create the calculation for the avg score grouping by subtheme per each client(case ID). I created a expected output based on some inputs values: As you can see, first all i need to sum the score per subtheme, then divide each client score for the subtheme which its belongs. I have tried several formulas, such as: Nothing works as expected: sometimes the values returned are the same as the score field for each client/caseid, or either it just calculate the sum of the score/count(total clients), what is not true, sice the nominator will variate depending of the score oh that or other specific client per suntheme each belongs. If someone know how to solve it. i would appreciate a lot. 🙂
Unable to edit scatter plot averages
Hi all, I am trying to change the colour of the line on my scatter plot and add data labels. For some reason, I am unable to edit them and it displays them as 'off', even though they are on my chart. I am also unable to delete the average lines or change their names - when I try they just reappear with the original name. The only way I have been able to delete averages is to reset to default. I have cleared my PowerBI caches, checked my version (2.137.1102.0 64-bit, October 2024) and restarted my laptop. I have other scatter graphs on the same page that are functioning correctly. Please help!How to calculate average of worktime from slicer date
Hi everyone. My problem is the present working time of an employee in the company. In the report, it has a date slicer, a count of working days, and the working hour of the employee in duration time. Currently, I want to present average of working day and average working hour in duration time from date slicer. My data below Table 'working_time' has columns: emloyee_id, workingdate, worktime_hrs (worktime hour in day), worktime_day (worktime day has default is 1) Depend on table sum worktime group by employee, the sum worktime is the total value divided by 5 employees. My expectation is the average of worktime_day should be (3+3+2+2+3) / 5 = 2.6 and the average of worktime_hrs should be (24+24+16+16+24) / 5 = 20.8. But I don't know how to calculate this average value. I hope everyone will spare their precious time to help me solve this problem. Thanks you so much!How to calculate average
Hi, I have 2 sheets, Sheet 1 – Result of evaluation – there is only 1 record per 1 person, and everyone has the evaluation result on the same date – 31/01/24 Example of raw data Units Result of evaluation Date Marketing 1 31/01/24 Marketing 3 31/01/24 Finance 2 31/01/24 Finance 2 31/01/24 Finance 3 31/01/24 Operations 3 31/01/24 Operations 3 31/01/24 Operations 4 31/01/24 Sheet 2- List of resign employees – which shows the ‘Result of evaluation’ that was merged from the sheet 1. Example of raw data Units Employee ID Result of evaluation Terminate Date Marketing 1111 1 31/01/24 Finance 3333 2 15/03/24 There is no relationship between these 2 sheets because once I create the relationship it said that it will cause ambiguity. And the one result that I’ve been longing for is to calculate the average of Result of evaluation so that I can calculate the attrition rate after this. This is what I have on Excel Sum of Result of evaluation 1-3 (4 was not counted) January - number of terminations in Jan No termination in February March - number of terminations in March Units January February March April Average Marketing 2 1 1 1 1.25 Finance 3 3 3 2 2.75 Operations 2 2 2 2 2 So after I got the average I can calculate the attrition rate like this (number of terminations / average) Units January February March April Marketing 50% (1/2) 67% (1/1.5) 75% (1/1.33) 80% (1/1.25) Finance 0% 0% 33% (1/3) 36% (1/2.75) Operations 0% 0% 0% 0% But now the current measure I got brings me the wrong answers, it's only correct when at the end of the year (e.g. only the attrition rate in December is correct) So please help suggest how to calculate the attrition like above table. Thank you in advance. 🙏🙏
