I have a table of shop floor production data structured as so:

job# category subcategory type qty
123 good good good 1768
123 defect internal form 22
123 defect internal trim 87
123 defect external film 66

Qty is my only measure, while job, category, subcategory, type, and others
make up my dimensions. I need to show defect percentage rates for whatever
is dragged onto an Excel pivot table. What is the best way to do that?
I've tried creating a calculated member but I can't figure out how to have
it total the defects, then divide that by good+defects. Should I
restructure my table, split the fact table into "defects" and "total
inspected" and join them in a virtual cube, or is there an MDX solution?

Thanks for the help.
Glen

Assuming that the [Category] dimension consists of [good] and [defect]
members, whose ratio determines the defect rate percentage, will a
measure like this work?

'([Measures].[Qty], [Category].[defect])
/([Measures].[Qty], [Category].[All Category])',
FORMAT_STRING = 'Percent'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

That's close. It returns the total defect rate percentage in each data
cell, instead of that cell's percentage.

For example:

Defect Defect Total Good Grand Total
Data External Internal
Qty 362 122 484 5279 5763
Defect Rate 0.083984036 0.083984036 0.083984036 0.083984036
0.083984036


What should I modify so each cell has its own fractional percentage of the
whole?

Thank you.
Glen

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

In that case, maybe this modification will work:

'([Measures].[Qty])
/([Measures].[Qty], [Category].[All Category])',
FORMAT_STRING = 'Percent'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

That works as expected. Thank you very much for the help. This is how I
felt when I was first learning Access and didn't understand SQL.

I'm using Microsoft Analysis Services to define the cube. It doesn't seem I
can format this calculated member as a percent and have it appear that way
in Excel. Is that true?

Thank you.
Glen

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote