I have a cube created via transformer which does a category count on supplier name. If I go into analysis studio and use the Supplier Count measure by country, everything works. It'll show something like:
United States - 100
Canada - 50
Mexico - 25
Total - 130
So I know that there are a total of 130 distinct suppliers being used across NA, some of them are used in multiple countries.
Now, if i want to determine the distinct number of suppliers used in just Mexico and Canada, it won't calculate the total properly. If I drag just Mexico and Canada into the Rows, it'll show this:
Mexico - 25
Canada - 50
Total - 130
It still shows the overall total. Same thing happens if I put Mexico and Canada into the Context Filter box and replace the rows with Year or something. It'll show:
Context Filter = Canada, Mexico
Year 2012 - 130
Total - 130
No matter what I try, I cannot get Analysis Studio to count the distinct number of suppliers using a combination of items within a level... it will always show the overall total for the highest level all items belong to.
Any ideas on how to get around this? The same issue is happening when I report using AS or Report Studio, so is this a cube limitation?
United States - 100
Canada - 50
Mexico - 25
Total - 130
So I know that there are a total of 130 distinct suppliers being used across NA, some of them are used in multiple countries.
Now, if i want to determine the distinct number of suppliers used in just Mexico and Canada, it won't calculate the total properly. If I drag just Mexico and Canada into the Rows, it'll show this:
Mexico - 25
Canada - 50
Total - 130
It still shows the overall total. Same thing happens if I put Mexico and Canada into the Context Filter box and replace the rows with Year or something. It'll show:
Context Filter = Canada, Mexico
Year 2012 - 130
Total - 130
No matter what I try, I cannot get Analysis Studio to count the distinct number of suppliers using a combination of items within a level... it will always show the overall total for the highest level all items belong to.
Any ideas on how to get around this? The same issue is happening when I report using AS or Report Studio, so is this a cube limitation?