Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating variance in a matrix

Posted on 2014-01-13
5
Medium Priority
?
2,866 Views
Last Modified: 2014-01-15
I have a chart that is displaying the Monthly Sales of the current fiscal year to the last fiscal year.  Now I am making a small table to the side with the actual $ figures of the monthly sales with a column showing the variance of one month this year to the same month last year.  Is there an easy way to do this?  I tried using the Var function but it doesn't calculate correctly.  What I need is to take the amount in the second column (2014) for each month/row and minus the amount in the first column (2013) for the same month/row.  Would it be easier to just layout a table with fixed columns and rows and then name the cells and figure it out that way?  I'd like to make it as dynamic as possible so I was hoping I could add a total column and then modify the formula to get the calculation I need but I'm not sure how to get that.  

Any help would be greatly appreciated!

Monthly sales
0
Comment
Question by:HSI_guelph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 39779069
You can try this expression:
=Last(Fields!Amount.Value) - First(Fields!Amount.Value)

Open in new window

But it depends on your data if this will work. The data needs to be summed by year and month already, and the data needs to be sorted by year (so first is 2013 and last is 2014). See the attached example.

If your data has another format it might still be possible to calculate the difference with a more complex formula. But to help you with that I need an example of your data.
MatrixVariance.rdl
0
 

Author Closing Comment

by:HSI_guelph
ID: 39780216
That did the trick!  I was sorting the data from May - April 2013, then May - April 2014.  Thank you very much!
0
 

Author Comment

by:HSI_guelph
ID: 39780226
Finished product
Though I think they'll want to see the total of the variance between the two years but I think I can do an if statement where if fiscal year is 2014, sum the sales minus a second if statement where if fiscal year is 2014, sum the sales.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39781460
When you use this formula:
=Sum(iif(Fields!Year.Value = Max(Fields!Year.Value), Fields!Amount.Value, Fields!Amount.Value - Fields!Amount.Value)) - Sum(iif(Fields!Year.Value = Min(Fields!Year.Value), Fields!Amount.Value, Fields!Amount.Value - Fields!Amount.Value))

Open in new window

It will work for both the detail and the total rows.
Max(Fields!Year.Value)

Open in new window

returns 14, and
Fields!Amount.Value - Fields!Amount.Value

Open in new window

returns 0 so the first part of the formula is like:
Sum(iif(Fields!Year.Value = 2014, Fields!Amount.Value,0))

Open in new window

Which is the sum of all the 2014 values. The second part of the formula  (after the - ) is the same but with min (so 2013).

I think displaying 0.00 for rows that only have data for one of the year is not correct. I think those cells should be empty. But this is easily solved by using a custom format like:
#,##0.00;(#,##0.00); ;

Open in new window

MatrixVariance.rdl
0
 

Author Comment

by:HSI_guelph
ID: 39782313
Thank you very much!!  That's awesome!  The boss will be impressed :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question