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

SSRS MATRIX InScope formula - get percentage of sales in group against total sales in row - HELP!

Posted on 2010-09-02
Last Modified: 2012-08-14
This is my most puzzling issue ever. I have 1 row with 4 nested groups below it. I inserted a total column to the left of the Column Group. The calculations (Sales and Pct) on the Total are correct. The Group has two body cells. Sales and Pct (percent of sales). Sales is the value in the expression. For each group, I want the percentage of sales against the total sales at each row group.
This is giving me an error.
=IIF(INSCOPE ("Matrix1_DivN")    AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_RegN")  AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_AreaN") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
 IIF (INSCOPE ("Matrix1_Store") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_StoreN") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  0  ))))  )

Help please! I havce been at this for two days and its due tomorrow!
Question by:Laura2112
LVL 14

Accepted Solution

Emes earned 500 total points
ID: 33618603
=IIF(INSCOPE ("Matrix1_DivN")    AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_RegN")  AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_AreaN") AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
 IIF (INSCOPE ("Matrix1_Store") AND INSCOPE  ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_StoreN") AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  0  ))))  )

Author Closing Comment

ID: 33618901
thanks so much!

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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