Calculate the average

[Beto_Zambom]

Good morning. 

On my spreadsheet I have in the same column a part number and the Working hours to replace that specific part number:

Follow an example

Job Number	Part Number	Qty
123	9001	1
123	118	2
456	9001	1
456	118	1.5
789	9001	1
789	118	1

The idea is to seach for the specifc part number "9001" and bring up the average of the time (118) take for replace the part (1.5)

[PowerBItes]

Do you require having it as a new column in our model? If not I would create a measure: 

Mesaure = 

AVERAGEX(
FILTER('Table', 'Table'[Part Number] = SelectedValue(Part Number)), 
'Table'[Working Hours])

Then add that measure to your visuals in Power BI where you want to display the average. You could create a table visual and use that measure as a column in that table. That way you aren't storing more data in your model as you would if you used a calculated column. 

At the top of the page just add a filter for the part number. Then you can drop down the filter, select the part number and see the average time to replace that part. You could filter it by mechanic, days of week, or whatever perspectives needed. I just used a single part number for the example. Using SelectedValue(Part Number) will do the calcualtion for whatever part number you select. 

[Beto_Zambom]

Not sure if I did the DAX correctly. 

Measure = AVERAGEX(FILTER(claim_export,claim_export[article_numbers]=SELECTEDVALUE(claim_export[article_numbers])),claim_export[quantity])

Where:

Claim_export is the name of my Table.

Article_Numbers is where the part number and the code for working hours are located.

Quantity is the amount of hours or amount of the parts have been used

Job Number	Article_Number	Qty
1340	90081639.1	1
1340	1111118	2
2291	90081639.1	1
2291	1111118	2
2623	90081639.1	1
2623	1111118	3
3543	90081639.1	1
3543	1111118	3

[Beto_Zambom]

Thanks for the answer! I reckon is more complicated than that. I use just one part number example, but imagine a spreadsheet with more than 5000 lines. With more than 1000 parts numbers listed.

What I want to do is to create a way where I search for the specific part number to check the average time to replaced. So I will be able to track with our mechanics specifics jobs and the "whys" for some jobs took longer than others. 

I created one measure to find the average, but when I search for the part number return Blancked:

Average = AVERAGEX(FILTER(claim_export,claim_export[article_numbers] = "1111118"),claim_export[quantity])

[PowerBItes]

Great question. You might need to do a little redesign for this to be the most performant. Here is what I would do. 

You can use DAX to calculate the average time taken to replace a specific part number in your spreadsheet. Here's an example measure that you can create in Power BI or Excel:

AvgTimeToReplace = 
AVERAGEX(
FILTER('Table', 'Table'[Part Number] = "9001"), 
'Table'[Working Hours])

In this measure, "Table" refers to the name of your table in Power BI or Excel. The FILTER function filters the table to only include rows where the Part Number is "9001", and the AVERAGEX function calculates the average of the Working Hours for these rows.

You can then display this measure in a table or chart alongside other relevant data. 

You can modify this by adding a variable for 9001 where the user selected the part number from a slicer and it updates the measure for the average of that part. 

Thanks for letting me help! If you found my response useful, please give me a thumbs up and consider visiting aimeetsanalytics.com for more insights and tips on data analytics.

[ryan_mayu]

@Beto_Zambom 

is this what you want?

Column = if('Table'[Part Number]="9001",AVERAGEX(FILTER('Table','Table'[Part Number]="118"),'Table'[Qty]),'Table'[Qty])