Getting Average based on Category and Countries

[Sidhu]

I have data in the the below format. I'm trying to get the average of SpareParts by Total, If I select a Location like DEF, it needs to get all the locations in that sector which is AB and also only the locations belongs to the same category which is A here. So it needs to eliminate line I from the result set. And it should do an average of (25% + 55%+ 3% + 50%) which is 33.25%

I am using this formula which is not getting the right result.

Here is the demo file.

Inventory Average =

Var Category1 = CALCULATE(MAX('FACT'[Category]),Line[Location]=SELECTEDVALUE(Line[Location]))

Var Sector= CALCULATE(MAX(Line[Sector]),Line[Location]=SELECTEDVALUE(Line[Location]))

var test =  CALCULATE(AVERAGEX('FACT',

                          SUM('FACT'[SpareParts])/SUM('FACT'[Total])

                          ),'FACT'[Category]=Category1,Line[Sector]=Sector,ALL('FACT')

                )

return test

[AlanFredes]

Hi Sidhu,

Try this. It will calculate the average correctly depending on the filterd data, as shown in the table you have.

AVERAGE has the bad habit of not calculating correctly when applying filters.
See this forum post for more detail.