Search code examples
reporting-servicesssrs-tablix

SSRS percentage of a total with column groups

I apologize in advance if this is not enough info or if it is confusing (This is my first post to the forum and it's a little hard to explain what i'm trying to do). I have been researching this for a couple days now. This article comes really close to what I need, but this person did not have column groups, just a a single column with an output: SSRS percentage of a total.

Consider this layout and groups:

Percent of Total Matrix and Groups

The idea is to provide the percentage of the total records per PrimaryPayor per Company and then break it out by Year-Month.

This expression: 

=CountDistinct(Fields!ID.Value, "Primary_Payor") / CountDistinct(Fields!ID.Value, "Company")

Results in this output (The first image is my output, the second is the desired output):

Results (Updated)

As you can see, the percentages do NOT group by Year-Month, instead just provides the total percentage of IDs per Payor per Company and repeats it for every Year-Month column. How can I get the percent of Total IDs per payor, company, and month as it shows in the second result image?

Solution

  • After editing my question, I thought of something I hadn't tried.

    If I just use =CountDistinct(Fields!ID.Value), this gives the total count for each payor, company, month, plus, I added a Total row.

    Then, if I reference those textbox values (instead of trying to calculate the percentage on the fly) divided by the total row, I get my results. I just had to add a column inside of the column group. I should then be able to just Hide my "total" column and display only the percentages.

    =ReportItems!Textbox50.Value / ReportItems!Textbox35.Value

    Here is the output (with the total counts and then after hiding the "count" column):

    Final Results