Incorrect Aggregations in Budget Matrix

Question

I am trying to create a matrix for our budget that allows us to see a side by side comparison. The first matrix table is a very small example of what the data should look like when grouped correctly. However, the second table matrix is what I end up with.I am using the following information in my configuration of the Matrix layout, however, I am assuming I am missing some important information in the calculations. Any help would be greatly appreciated.&nbsp;Calculations:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BudgetRevenue =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE( SUM( CustomerRevBudget[RevenueBudget] ) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ActualRevenue = SUM( CustomerActualRevenue[TotalRevenue] )&nbsp;Rows:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Company&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SalesRepColumns:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Calendar Quarter&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Calendar MonthValues:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BudgetRevenue (Measure)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Actual Revenue (Measure)&nbsp;&nbsp;Desired:&nbsp;&nbsp;Calendar QuarterQ1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Quarter Subtotals&nbsp;&nbsp;&nbsp;Calendar MonthJan&nbsp;Feb&nbsp;Month Subtotals&nbsp;&nbsp;&nbsp;ModeCompanySales RepBudget RevenueActual RevenueBudget RevenueActual RevenueBudget RevenueActual RevenueBudget RevenueActual RevenueAirCompany ASales Rep 110001500100012002000270020002700&nbsp;Company BSales Rep 220002700200040004000670040006700&nbsp;Company CSales Rep 3&nbsp;3000300018003000480030004800ExpediteCompany ASales Rep 120003000200024004000540040005400&nbsp;Company BSales Rep 24000540040008000800013400800013400&nbsp;Company CSales Rep 3&nbsp;6000600036006000960060009600TOTALS&nbsp;&nbsp;900021600180002100027000426002700042600&nbsp;Actual:&nbsp;&nbsp;Calendar QuarterQ1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Quarter Subtotals&nbsp;&nbsp;&nbsp;Calendar MonthJan&nbsp;Feb&nbsp;Month Subtotals&nbsp;&nbsp;&nbsp;ModeCompanySales RepBudget RevenueActual RevenueBudget RevenueActual RevenueBudget RevenueActual RevenueBudget RevenueActual RevenueAirCompany ASales Rep 120001500200012004000270040002700&nbsp;&nbsp;Sales Rep 22000&nbsp;2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 32000&nbsp;2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company BSales Rep 14000&nbsp;4000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 240002700400040008000670080006700&nbsp;&nbsp;Sales Rep 34000&nbsp;4000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company CSales Rep 13000&nbsp;3000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 23000&nbsp;3000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 330003000300018006000480060004800ExpediteCompany ASales Rep 140003000400024008000540080005400&nbsp;&nbsp;Sales Rep 24000&nbsp;4000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 34000&nbsp;4000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company BSales Rep 18000&nbsp;8000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 2800054008000800016000134001600013400&nbsp;&nbsp;Sales Rep 38000&nbsp;8000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Company CSales Rep 16000&nbsp;6000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 26000&nbsp;6000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales Rep 36000600060003600120009600120009600TOTALS&nbsp;&nbsp;8100021600690002100054000426005400042600

anonymous · Answer

... and so, you certainly think that somebody will tell you what's wrong without having any idea whatsoever about what your model looks like? :) Well, miracles do happen sometimes but a reasonable person should never count on one.

Best

Darek

anonymous · Answer

Actual Revenue Data Table:ModeCompanySalesRepActual RevenueMonthAirCompany ASales Rep 1750JanAirCompany ASales Rep 1750JanExpediteCompany ASales Rep 11500JanExpediteCompany ASales Rep 11500JanAirCompany ASales Rep 1600FebAirCompany ASales Rep 1600FebExpediteCompany ASales Rep 11200FebExpediteCompany ASales Rep 11200FebAirCompany BSales Rep 21350JanAirCompany BSales Rep 21350JanExpediteCompany BSales Rep 22700JanExpediteCompany BSales Rep 22700JanAirCompany BSales Rep 22000FebAirCompany BSales Rep 22000FebExpediteCompany BSales Rep 24000FebExpediteCompany BSales Rep 24000FebAirCompany CSales Rep 31500JanAirCompany CSales Rep 31500JanExpediteCompany CSales Rep 33000JanExpediteCompany CSales Rep 33000JanAirCompany CSales Rep 3900FebAirCompany CSales Rep 3900FebExpediteCompany CSales Rep 31800FebExpediteCompany CSales Rep 31800Feb&nbsp;Revenue Budget Table:ModeCompanyBudget RevenueMonthAirCompany A1000JanExpediteCompany A2000JanAirCompany A1000FebExpediteCompany A2000FebAirCompany B2000JanExpediteCompany B4000JanAirCompany B2000FebExpediteCompany B4000FebAirCompany C&nbsp;JanExpediteCompany C&nbsp;JanAirCompany C3000FebExpediteCompany C6000Feb

Forum Discussion

Incorrect Aggregations in Budget Matrix

2 Replies

Related content

Working with Budgets

Incorrect turnover total

Budget, actual and variance matrix

Data Aggregation issue

Monthly sales budget data allocation to daily grain

		Calendar Quarter	Q1						Quarter Subtotals
		Calendar Month	Jan		Feb		Month Subtotals
Mode	Company	Sales Rep	Budget Revenue	Actual Revenue	Budget Revenue	Actual Revenue	Budget Revenue	Actual Revenue	Budget Revenue	Actual Revenue
Air	Company A	Sales Rep 1	1000	1500	1000	1200	2000	2700	2000	2700
	Company B	Sales Rep 2	2000	2700	2000	4000	4000	6700	4000	6700
	Company C	Sales Rep 3		3000	3000	1800	3000	4800	3000	4800
Expedite	Company A	Sales Rep 1	2000	3000	2000	2400	4000	5400	4000	5400
	Company B	Sales Rep 2	4000	5400	4000	8000	8000	13400	8000	13400
	Company C	Sales Rep 3		6000	6000	3600	6000	9600	6000	9600
TOTALS			9000	21600	18000	21000	27000	42600	27000	42600

Mode	Company	SalesRep	Actual Revenue	Month
Air	Company A	Sales Rep 1	750	Jan
Air	Company A	Sales Rep 1	750	Jan
Expedite	Company A	Sales Rep 1	1500	Jan
Expedite	Company A	Sales Rep 1	1500	Jan
Air	Company A	Sales Rep 1	600	Feb
Air	Company A	Sales Rep 1	600	Feb
Expedite	Company A	Sales Rep 1	1200	Feb
Expedite	Company A	Sales Rep 1	1200	Feb
Air	Company B	Sales Rep 2	1350	Jan
Air	Company B	Sales Rep 2	1350	Jan
Expedite	Company B	Sales Rep 2	2700	Jan
Expedite	Company B	Sales Rep 2	2700	Jan
Air	Company B	Sales Rep 2	2000	Feb
Air	Company B	Sales Rep 2	2000	Feb
Expedite	Company B	Sales Rep 2	4000	Feb
Expedite	Company B	Sales Rep 2	4000	Feb
Air	Company C	Sales Rep 3	1500	Jan
Air	Company C	Sales Rep 3	1500	Jan
Expedite	Company C	Sales Rep 3	3000	Jan
Expedite	Company C	Sales Rep 3	3000	Jan
Air	Company C	Sales Rep 3	900	Feb
Air	Company C	Sales Rep 3	900	Feb
Expedite	Company C	Sales Rep 3	1800	Feb
Expedite	Company C	Sales Rep 3	1800	Feb