Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blacky35
Starting Member
1 Post |
 Posted - 2012-05-18 : 17:14:28
|
Hello -For the last two days I have been struggling to figure out how to divide two cells in a Matrix. Maybe this is the wrong way to think about it....another way of saying it...dividing a conditional total sum by a total row sum from the data set. Here is where I am at:I have the following matrix built:[URL=http://imageshack.us/photo/my-images/707/testmatrixdesignview.png/] [/URL]And here are the results:[URL=http://imageshack.us/photo/my-images/716/testmatrixpreview.png/] [/URL]I need to build an expression to populate the G% column correctly, which is simply (SUM(Sales) WHERE Indicator = "G")/SUM(Sales). So in this example it would be the following calculations:23,386 / 28,574 = 81.84%23,602 / 28,810 = 81.92%11,839 / 16,025 = 73.88%12,476 / 16,837 = 74.10%12,023 / 15,974 = 74.27%So far my expression looks like this: =(IIF(Trim(Fields!Indicator.Value) = "G",Fields!Sales.Value, 0)/SUM(Fields!Sales.Value))But it is obviously returning 0.00% for all fields. Another funky thing is if I change the expression to look for Indicator.Value = "B" it seems to work just fine:[URL=http://imageshack.us/photo/my-images/521/testmatrixpreviewb.png/] [/URL]Any help would be greatly appreciated! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-19 : 14:57:35
|
| you need to apply two row groups here. outer group on Client and next group on yearmonth field------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Divide Two Cells in a Matrix