I have a fact measure group called production it is linked to a fact dim Production details. This contains production information at line and header level. If i am selecting line level items i expect the grand totals for headers to sum the distinct value (not sum dupes) for each order. There doesn't seem to be an option in excel to specify which columns you want to grand total when connected to a cube. How do i get around this?
This is the wrong design. I would change your cube and move the production header level metrics to a new fact table which has all the same dimension keys except for the Component Item dimension.
You can look for a property on the measure group called IgnoreUnrelatedDimensions. The default will repeat the header value for each item but not double count for the subtotals. Changing that setting to False will make the header values null at the item level.
But I suspect instead of using this setting you should use a many-to-many Component Item dimension similar to how we have discussed in the past.