Calculate with ALL and FILTER on different tables

[Sibrulotte]

Hi,

I would like to calculate this:

CALCULATE(Sum('Balance'[Market value]), ALL('State'[StateName]),Filter('Balance', 'Balance'[Year]=max('Balance'[Year])))

Basically, sum the market value for all states (even when there is a filter) but filter it so that only the max year is taken into account.

[v-xulin-mstf]

Hi @Sibrulotte,

Try measure as:

Measure = 
SUMX(VALUES('Table'[Delivery Date]),CALCULATE(Sum('Balance'[Market value]), Filter('Balance', 'Balance'[Year]=MAXX(Balance','Balance'[Year]))))

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Sibrulotte]

Hi, 

thank you for taking the time to respond!

I'm not quite sure what Table [Delivery date] does in the formula, so I can't realy replicate it since I'm not sure how to transpose it.

Year	StateName	Average Age	Average Balance	Market value
2020	NY	40	100	5000
2021	NY	50	300	15000
2020	KY	50	400	10000
2021	KY	60	600	20000
2020	CA	20	50	100
2021	CA	30	150	300
2020	FL	40	500	20000
2021	FL	50	1500	40000
2020	TX	50	1200	6000
2021	TX	40	600	1000

and the result would be 76300 even if I have FL selected in a slicer

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-xulin-mstf]

Hi @Sibrulotte,

Try measure as:

Measure = 
SUMX(VALUES('State'[StateName]),CALCULATE(Sum('Balance'[Market value]), Filter('Balance', 'Balance'[Year]=MAXX(Balance','Balance'[Year]))))

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!