HELP WITH SUM FORMULA

[robg89]

I need urgent help on a SUM formula please!

I am trying to make a matrix table which sums up a number for the entire year, even when the table shows quarterly periods.

For example, there is $5k of sales to renew across the whole of 2022, with differing amounts per quarter. I would like the output of my formula to show $5k for each of the quarters and not their individual portfolio . I have added the formula below which I thought should work.

I have added a summary of the data below, thanks in advance!

[FreemanZ · Accepted solution]

hi @robg89 

try to plot the a like:

Amount = 
CALCULATE(
	SUM(Table1[Portfolio]),
	FILTER(
		ALL(Table1[Qtr]),
		YEAR(Table1[Qtr])=YEAR(MAX(Table1[Qtr]))
	)
)

[robg89]

After looking at the data again, I still need help. If I look at it by month, the months where there are no portfolios are blank, I need these to also show $5k. Can you please help?

[FreemanZ]

hi @robg89 

could provide your updated data table?

[robg89]

The data hasn't changed. I am trying to look at the portfolio values monthly therefore in the above example, I would like to see $5k in every month.

To put in to practical terms, at the start of the year (opening portfolio) I have $5k,this renews across 4 quarters. Every month we have sales adding to the portfolio but I want the opening portfolio to stay the same, 

[FreemanZ]

hi @robg89 

then you need another table, with all the MonthEndDate and connected with your datatable. 

Feed your visual with MonthEndDate column.