Mdx - Sum quantities over id - distinct sum

[chevito]

Hi all,

I have the next scenario:

I have a factories table, with factoryId, NumEmployees, NumFemaleEmployees, NumMaleEmployees and countryId (among others). Then I have this other table called province workers with ProvinceWorkerId, FactoryId, WorkerCount, Province (text). I am working on a OLAP cube, and I have created a fact table with this info:

FactoryId, NumEmployees, NumMaleEmployees, NumFemaleEmployees, CountryId, WorkerCount, Province.

My issue, is when I try to make sums. I end up with registries like:
FctyId #Employees #Female #Male Cty WorkerCount Province
1 100 70 30 2 12 Arizona
1 100 70 30 2 30 Nebraska
1 100 70 30 2 20 Texas
2 200 60 140 2 30 Arizona

When I get the sum of WorkerCount, everything is ok, but getting the NumEmployees sums, does not work, because it is retrieving the repeated data. I don't know how to do in MDX to sum up by FactoryId, I mean adding that measure.

I have two limitations right now, I can only use one fact table, and the measures must be in that fact table (because of the control that I am using). Maybe I have a design error (this is my very first cube), so any suggestions would be welcome, and if this has been answered before, the link could help me a lot.

Thanks, and have a good day.

 

[PWise]

is this an access forms question?

 

[chevito]

Well, I am using the 3rd party Radar Soft component (Asp.Net MVC), and I just created an sql view as my fact table. In the Radar Soft documentation, it is stated that I can use MDX queries, so that is kinda of what I am searching for. Create a calculated measure, and put the MDX expression to calculate it.