Calculate+filter+max error

[Anonymous]

I am just using below formula to calculate Rev/Sqft by  Catogory  by DataMth (data month)

00This_month_R.S = CALCULATE( ('Table'[00Rev]/'Table'[Sqft]),filter(ALLSELECTED('Table'),'Table'[DataMth]<=max('Table'[DataMth])))

I found that when I choose one catogory, then answer is correct, totally the same with excel answer.

	DataMth
Catogory	201911	201912	202001	202002
A	705.2295	705.2643	528.5895	448.5803

But when I choose all catogory, found that all column data are the same, did not filter  by catogory, is anything missed in my formula ?  thanks a lot.

	DataMth
Catogory	201911	201912	202001	202002
A	393.8481	424.5263	373.781	337.5545
E	393.8481	424.5263	373.781	337.5545
G	393.8481	424.5263	373.781	337.5545
H	393.8481	424.5263	373.781	337.5545

[amitchandak]

@Anonymous 

Move month into a separate table

Try

This_month_R.S = CALCULATE( divide('Table'[00Rev],'Table'[Sqft]),filter(ALLSELECTED('Month'),'Table'[DataMth]<=max('Month'[DataMth])))

Or
This_month_R.S = CALCULATE( divide(sum('Table'[00Rev]),sum('Table'[Sqft])),filter(ALLSELECTED('Month'),'Table'[DataMth]<=max('Month'[DataMth])))

[Anonymous]

Thank you, I tried, but seems result is not correct, smaller more than it should be.

[az38 · Accepted solution]

@Anonymous 

00This_month_R.S = CALCULATE( ('Table'[00Rev]/'Table'[Sqft]), filter(ALLEXCEPT('Table', 'Table'[Catogory]), 'Table'[DataMth]<=max('Table'[DataMth])))

[Anonymous]

Thank you, it does work!