Percentages in a matrix data cell

  • 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

  • hi,

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

  • 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)

  • 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.

  • Sorry I was looking at the wrong report

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

  • 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)

  • 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