I have created a matrix that looks like the following example:
APRIL MAY JUNE
PRODUCT X 10 20 30
PRODUCT Y 20 30 40
where the data is profit per unit sold (=PROFIT/# of UNITS)
I need to add a row that is the average profit for each month. I know
how to get the subtotal row to show up, but this justs adds the rows
(which is meaningless for me). Even the simple average (e.g. (10 +
20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
for APRIL/ALL UNITS sold for APRIL). A data example is:
for April I sold 10 units of X for a $100 profit and I sold 20 units
of Y for a $400 profit
As in the table above, the profit per unit is (100/10) $10 for X and
(400/20) $20 for Y
But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
profit / 30 units = ~$17
So two questions:
How do I add a average row to the matrix?
How do I make this a weighted average?
Sorry if my explanation is confusing or the answer is basic... new to
this and can't find an answer anywhere!It sounds like your current expression is something like this:
=Sum(Fields!ProfitPerUnit.Value)
What you really need is something more like this:
=Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
If you are only returning ProfitPerUnit and Units in your query, you could
do this instead:
=Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0407151323.79162246@.posting.google.com...
> I have created a matrix that looks like the following example:
> APRIL MAY JUNE
> PRODUCT X 10 20 30
> PRODUCT Y 20 30 40
> where the data is profit per unit sold (=PROFIT/# of UNITS)
> I need to add a row that is the average profit for each month. I know
> how to get the subtotal row to show up, but this justs adds the rows
> (which is meaningless for me). Even the simple average (e.g. (10 +
> 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> for APRIL/ALL UNITS sold for APRIL). A data example is:
> for April I sold 10 units of X for a $100 profit and I sold 20 units
> of Y for a $400 profit
> As in the table above, the profit per unit is (100/10) $10 for X and
> (400/20) $20 for Y
> But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> profit / 30 units = ~$17
> So two questions:
> How do I add a average row to the matrix?
> How do I make this a weighted average?
> Sorry if my explanation is confusing or the answer is basic... new to
> this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!
RLGow wrote: