Calculate averages for each distinct item in a table then get the sum

[matthewjd24]

Hello, I'm struggling with something simple but I'm not really sure what it's called, so it's hard to find any information about it.

My manufacturing company is using Power BI to look at parts recently manufactured. We have a fact table that records the status of our machines, and from this table I'm trying to create visualizations. Right now I'm working on having it calculate how much it costs us in tools when we run a part. We have many parts, and each part has multiple "operations" it has to undergo before it's a finished product. Every time we start a new part, the PartInstance column goes up by 1. My task is to figure out how to calculate the average cost (sum of $SpentOnTool) per PartInstance, which I've made a measure to do- SUM($SpentOnTool) / DISTINCTCOUNT(PartInstance)

There are 3 part numbers visible here, #23, 7, and 54. Right now, you have to filter by one part number and one operation number for the measure to work. Then my measure gets the sum of $SpentOnTool, the distinct count of PartInstance, and then divides the two to get average cost per for the Part and Operation you've selected. However, this measure only works if you have one operation number selected.

If you select multiple OperationNumbers, I need to have the measure show the sum of the tool cost for each operation number. I'm not sure how to do that. Here is a simple table with how the math should work.

But if you select operation 1, 2, and 3 together, it's doing $7.84 / 44 = $0.18. It needs to be instead $0.07 + $0.79 + $0.06 = $0.96, but I'm struggling to figure out how to do that. Any ideas? Thank you.

[matthewjd24 · Accepted solution]

I figured out the solution. I found out about the SUMMARY function and I used that and SUMX to do what I needed to do. To anybody in the future with the same problem, I created the following measure

AvgToolCostPerPart =

var summaryTable = SUMMARIZE('MachineData',MachineData[PartNumber],MachineData[OperationNumber],"AvgOpCost", (sum(MachineData[$SpentOnTool]) / DISTINCTCOUNT(MachineData[PartInstance])))

return sumx(summaryTable, [AvgOpCost])

It basically does what I had been describing- finding the average cost per instance for each PartNum + OpNum and creating a table with that. Then SUMX adds the resulting values together. 

Useful links I found:

https://stackoverflow.com/questions/41170063/sum-of-a-column-resulting-from-summarize-function-in-da...

https://docs.microsoft.com/en-us/dax/summarize-function-dax

https://community.powerbi.com/t5/Desktop/Calculating-distinct-count-of-items-based-on-average-sales/... 

[Fsciencetech]

Could you please share sample data

[matthewjd24]

Hi, thank you helping, I have edited the post to include sample data and hopefully better explain my goal. Thank you.