Forum Discussion
Calculate+filter+max error
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 |
Anonymous
00This_month_R.S = CALCULATE( ('Table'[00Rev]/'Table'[Sqft]), filter(ALLEXCEPT('Table', 'Table'[Catogory]), 'Table'[DataMth]<=max('Table'[DataMth])))
4 Replies
Anonymous
00This_month_R.S = CALCULATE( ('Table'[00Rev]/'Table'[Sqft]), filter(ALLEXCEPT('Table', 'Table'[Catogory]), 'Table'[DataMth]<=max('Table'[DataMth])))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])))
