Getting Average based on Category and Countries

Sidhu — Fri, 30 Jun 2023 20:32:51 GMT

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

Re: Getting Average based on Category and Countries

AlanFredes — Sun, 02 Jul 2023 02:53:31 GMT

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.

topic Re: Getting Average based on Category and Countries in DAX Commands and Tips

Getting Average based on Category and Countries

Re: Getting Average based on Category and Countries