topic Re: HELP WITH SUM FORMULA in DAX Commands and Tips

HELP WITH SUM FORMULA

robg89 — Sun, 02 Apr 2023 11:18:19 GMT

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!

Re: HELP WITH SUM FORMULA

FreemanZ — Sun, 02 Apr 2023 11:53:06 GMT

hi robg89

try to plot the a like:

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

Re: HELP WITH SUM FORMULA

robg89 — Sun, 02 Apr 2023 12:06:01 GMT

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?

Re: HELP WITH SUM FORMULA

FreemanZ — Sun, 02 Apr 2023 12:19:59 GMT

hi robg89

could provide your updated data table?

Re: HELP WITH SUM FORMULA

robg89 — Sun, 02 Apr 2023 12:23:58 GMT

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,

Re: HELP WITH SUM FORMULA

FreemanZ — Sun, 02 Apr 2023 13:12:14 GMT

hi robg89

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

Feed your visual with MonthEndDate column.