Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robg89
Regular Visitor

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!

robg89_1-1680433744273.png

 

 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @robg89 

try to plot the a like:

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

FreemanZ_0-1680436351633.png

 

View solution in original post

5 REPLIES 5
FreemanZ
Super User
Super User

hi @robg89 

try to plot the a like:

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

FreemanZ_0-1680436351633.png

 

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?

robg89_0-1680437148449.png

 

hi @robg89 

could provide your updated data table?

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, 

hi @robg89 

 

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

 

Feed your visual with MonthEndDate column. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors