Forum Discussion

robg89's avatar
robg89
Regular Visitor
3 years ago
Solved

HELP WITH SUM FORMULA

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!

 

 

@Dax help
@Power BI
@sumx
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi robg89 

    try to plot the a like:

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

     

5 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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's avatar
      robg89
      Regular Visitor
      3 years ago

      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?