Post reply

Percentage Difference From Minimum in Grouping Calculation

  • November 12, 2015 at 9:41 am

    #302292

    Hello Folks,

    I have a brain block today when trying to calculate a percentage difference from Minimun value of a group of values for a specific grouping.

    I have a group and for each group of values I find the minimum which I post at the bottom of the group as shown in the picture, but also for each of the individual values, I have to find the percentage difference from that Min found. I cant seem to use the following calculation as it finds the min at the row level, not at the column level.

    IIF(((Fields!BALANCE.Value * 100) / MIN(Fields!BALANCE.Value)) > 100

    AND ((Fields!BALANCE.Value * 100) / MIN(Fields!BALANCE.Value)) > Parameters!Percentage.Value,

    ((Fields!BALANCE.Value * 100) / MIN(Fields!BALANCE.Value)) - 100, Nothing)

    If the percentage is greater than a parameter specified number, I want to display the percentage difference, otherwise i dont want to display nothing as noted in the image below.

    In the image above, 30 would be the minimum for the entire group, which later i have to use at the row level to calculate the percentage difference higher from the minimum for each row.

    Fields!BALANCE.Value is the Plan House Cost field in the picture. Parameters!Percentage.Value is the parameter that determines the percentage value wanted by the user.

    Also, I am using a regular table with one grouping only. No Matrix.

  • November 13, 2015 at 3:54 am

    #1839561

    Hi,

    Your picture didn't attach correctly but I suspect that you should explicitly scoping your Min function, probably over the tablix or dataset.

    For example Min(Fields!BALANCE.Value, "Tablix1") or Min(Fields!BALANCE.Value, "DataSet1") where Tablix1 or DataSet1 is the name of your tablix or dataset.

    If no scope is specified for a summary SSRS will default the scope to the group the expression is contained in. If you need a summary to be aggregated over a parent group or object you can specify that scope. Scopes can be group names or include objects such as the Tablix or Dataset.

Viewing 2 posts - 1 through 1 (of 1 total)

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