Crosstab Aggregation Calculations

[cmedved]

Hi. I'm experiencing some difficulty with aggregation and was hoping I could get some help.

I attached an image of the structure of the crosstab I am trying to create. Here is also a bad text version...

If LOB1 has 1000 rows, LOB2 has 2000 rows, and LOB3 has 100 rows for Last Week...

                       Last Week                                                Two Weeks Ago                                    .....
         HEALTH     Normalized Value                          Health                Normalized Value
LOB1     50%       .5 * 1000 / 3100 = 16.12%            40%                       .....
LOB2     50%       .5 * 2000 / 3100 = 32.25%            30%                       .....
LOB3     10%       .1 * 100 / 3100 = 0.32%                20%                       .....

I get my data from a query - not from the model. The data from the query looks like rows of:

logdate,LOB,HealthScore

It is NOT aggregated data - there can be duplicate rows, since I am only selecting a few of the columns. This is intended.

Week is a calculated value based off the logdate and looks like 'This Week', 'Last Week', etc.

Week and LOB are both categories. HealthScore is a measure. I need to create a calculated item off of the health score as a normalized version. The calculation should be HealthScore * LOBJobs / TotalJobs, where LOBJobs is the number of rows for a LOB in a week and TotalJobs is the sum of LOBJobs for each week, or a count of the rows during each week (they are equivalent).

Load is a placeholder for the moment. It will be the normalized score.

I've tried using the "within detail" clause of Total and Count, but I have not gotten anything to work. Is it possible to get those values when using a crosstab and use them in a calculation? I can use a simple count calculation to get the row count for each LOB for each Week, but I don't know how I can then add those up.