Hi everyone, my question is related to creating a calculated member that must not change when the user changes the selected filter in the dimensions.
I will try to explain it: I need the total number of rows of my cube for a concrete year (year is a dimension). I will use this value in a calculated formula to define the percentage of rows that contains a concrete value (selected in the filter dimension). It means that my calculated member has a factor that change with the filter (ie. number of rows with value 'XXX'), divide by a factor that not change (total number of rows per year).
I have tried in many different ways, but I did not succeed. I cannot believe this is not possible...
Hi Brett, I understand your question since this is quite strange, nevertheless, I will try to explain.
The main reason is that I need to detect the quality of the data (used to join with the dimensions) in the fact table . The formula to do that is getting the rate of rows with the correct information. One way to do it will be just dividing the total number of rows by the number of rows with dimension column equals null.
I can calculate the total number of rows creating a calculated member like "count([mydimension].AllMembers)", but this number of rows is true only for the dimension "mydimension". Other possibility is using a column with value 1 for every row, and doing a sum([mydimension].AllMembers,mycolumn1), but this is true only when I filter for mydimension.
So, the real question is, how can I get the real total number of rows, valid for any dimension?.