Incorrect Aggregations in Budget Matrix

[mcornejo_1]

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.

Calculations:

          BudgetRevenue =

                CALCULATE( SUM( CustomerRevBudget[RevenueBudget] ) )

           ActualRevenue = SUM( CustomerActualRevenue[TotalRevenue] )

Rows:

         Mode

         Company

         SalesRep

Columns:

         Calendar Quarter

         Calendar Month

Values:

          BudgetRevenue (Measure)

          Actual Revenue (Measure)

Desired:

		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

Actual:

		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	2000	1500	2000	1200	4000	2700	4000	2700
		Sales Rep 2	2000		2000
		Sales Rep 3	2000		2000
	Company B	Sales Rep 1	4000		4000
		Sales Rep 2	4000	2700	4000	4000	8000	6700	8000	6700
		Sales Rep 3	4000		4000
	Company C	Sales Rep 1	3000		3000
		Sales Rep 2	3000		3000
		Sales Rep 3	3000	3000	3000	1800	6000	4800	6000	4800
Expedite	Company A	Sales Rep 1	4000	3000	4000	2400	8000	5400	8000	5400
		Sales Rep 2	4000		4000
		Sales Rep 3	4000		4000
	Company B	Sales Rep 1	8000		8000
		Sales Rep 2	8000	5400	8000	8000	16000	13400	16000	13400
		Sales Rep 3	8000		8000
	Company C	Sales Rep 1	6000		6000
		Sales Rep 2	6000		6000
		Sales Rep 3	6000	6000	6000	3600	12000	9600	12000	9600
TOTALS			81000	21600	69000	21000	54000	42600	54000	42600

[mcornejo_1]

Actual Revenue Data Table:

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

Revenue Budget Table:

Mode	Company	Budget Revenue	Month
Air	Company A	1000	Jan
Expedite	Company A	2000	Jan
Air	Company A	1000	Feb
Expedite	Company A	2000	Feb
Air	Company B	2000	Jan
Expedite	Company B	4000	Jan
Air	Company B	2000	Feb
Expedite	Company B	4000	Feb
Air	Company C		Jan
Expedite	Company C		Jan
Air	Company C	3000	Feb
Expedite	Company C	6000	Feb

[Anonymous]

... 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