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 $...
@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]))
	)
)

     

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?

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi robg89 

could provide your updated data table?

  • robg89's avatar
    robg89
    Regular Visitor
    3 years ago

    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's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi robg89 

       

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

       

      Feed your visual with MonthEndDate column.