I have 2 fact tables, 1 contains volume of goods sold, and a month key. Each record is for a different store location.
The second fact table contains the number of business days in a month, and the month key.
Now I want to produce a report to show number of business days and the volume for that month. However runing a report returned all NBD records even If there was no volume for that month. So I created a calculated measure to return the NBD only if we had volume.
IIF ( [Measures].[TOTAL VOL], [Measures].[NUM BDS QTY], NULL )
This worked fine, but the NBD totals were wrong. The system totaled the entire NBD table instead of totaling the values returned by my calculated measure.
Does anyone have any ideas for correcting this, or is there another way to do this?
Thanks, Borf
The second fact table contains the number of business days in a month, and the month key.
Now I want to produce a report to show number of business days and the volume for that month. However runing a report returned all NBD records even If there was no volume for that month. So I created a calculated measure to return the NBD only if we had volume.
IIF ( [Measures].[TOTAL VOL], [Measures].[NUM BDS QTY], NULL )
This worked fine, but the NBD totals were wrong. The system totaled the entire NBD table instead of totaling the values returned by my calculated measure.
Does anyone have any ideas for correcting this, or is there another way to do this?
Thanks, Borf