Calculate two columns with the average of one and the sum of another based on third column

[WolfDog23]

Hi,

I'm trying to figure out how to display a remaining the remaining capacity in a tool. I have a column which tells you the tool capacity from a lookup table. I have another column which tell you have many parts are in the tool for a certain test. I tried to subtract these two values and filter them by a third column which tell me the tests status as "Running". The issue I'm having is if there is more than one test running in the same tool, it will sum the capacity value by the number of test and subtract that number from the sum of parts running per test. For example if two tests are running 20 parts and the capacity is 100, instead of showing 60 parts remaining capacity, it's showing 160. I have a column forTest ID, Tool Name, Dut Capacity, Parts Running and Test Status. Does anyone know how to only have the lookup value averaged instead of added and subtracted from the parts running based on Tool Name and Parts Running? Thanks in advanced for any help!

[ichavarria · Accepted solution]

Hi @WolfDog23,

To solve this problem, you can use the AVERAGE function in Power BI to calculate the average capacity of the tool, and then subtract the total number of parts running from that average value. You will have to follow this steps:

1. Create a new measure by going to the Modeling tab and selecting New Measure.
2. Name the measure "Remaining Capacity" or something similar.
3. Enter the following formula:

Remaining Capacity = [Tool Capacity] - AVERAGEX(FILTER(Test, Test[Status] = "Running"), [Parts Running])

In this formula, [Tool Capacity] refers to the column in your lookup table that contains the capacity of each tool, and [Parts Running] refers to the column that contains the number of parts running for each test.

The FILTER function is used to filter the tests to only include those with a status of "Running". The AVERAGEX function then calculates the average capacity of the tool based on the filtered tests. Finally, this average value is subtracted from the tool capacity to give you the remaining capacity.

By using AVERAGEX instead of SUMX, you are ensuring that the tool capacity is only averaged once, even if multiple tests are running on the same tool.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly