Post reply

Percentages in a matrix data cell

  • October 21, 2009 at 6:55 am

    #212238

    HI please help

    I have a matrix like so:

    African White Coloured

    Female Male Female Male Female Male Total

    Senior Manager 5 6 4 9 3 5 36

    Junior Manager 10 12 9 13 6 8 58

    What I need to do is take the contents of the cell and devide them by the total to get the percentage.

    I tried these but I'm not winning:

    =iif(inscope("matrix1_gender") and inscope("matrix1_race"),

    FormatPercent (Sum(Fields!Cnt.Value)/Sum(iif(not inscope("matrix1_gender") and not inscope("matrix1_race"), Fields!Cnt.Value, "0.00%"))),2)

    it returns 100% for everything.

    =iif(inscope("matrix1_gender") and inscope("matrix1_race"),

    FormatPercent (Fields!Cnt.Value)/Sum(iif(not inscope("matrix1_gender") and not inscope("matrix1_race"), Fields!Cnt.Value, "0.00%")),2)

    it returns 1 for everything

    =iif(inscope("matrix1_gender") and inscope("matrix1_race"),

    FormatPercent((Fields!Cnt.Value)/Sum(Fields!Cnt.Value), "0.00%"),2)

    it returns #error in every cell

    Please help

  • October 23, 2009 at 3:46 am

    #1069885

    hi,

    what are trying to do with your "iff" function?

  • October 23, 2009 at 10:58 am

    #1070171

    On the sum function you can specify the scope.

    Try this to do the percentage over gender:

    =iif(inscope("matrix1_gender") and inscope("matrix1_race"),

    FormatPercent(sum(Fields!Cnt.Value)/Sum(Fields!Cnt.Value,"matrix1_gender"), "0.00%"),2)

  • October 26, 2009 at 8:16 am

    #1070714

    Hi I tried your suggestion but I still get an error

    the value expression for the textbox 'staffcount' has a scope parameter that is not valid for an aggregate function.

  • October 26, 2009 at 8:22 am

    #1070721

    Sorry I was looking at the wrong report

    but I'm still getting an error saying : input string was not in a correct format

  • October 26, 2009 at 5:40 pm

    #1070985

    Sorry, I didn't see that FormatPercent function is wrong. This should work:

    =iif(inscope("matrix1_gender") and inscope("matrix1_race"),

    FormatPercent(sum(Fields!Cnt.Value)/Sum(Fields!Cnt.Value,"matrix1_gender")),2)

  • October 27, 2009 at 1:43 am

    #1071054

    Thanks

    it is working wonders

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply