question

RyanAnderson-3228 avatar image
0 Votes"
RyanAnderson-3228 asked CarrinWu-MSFT commented

SSAS - Percent of total dimension member across all measures

I have multiple dimensions which require members that return a % of total result. An example of one of these dimensions is below, the bold members are the ones that I am trying to create. The idea is that i can then filter this dimension by any of my measures.

Sex Dimension:

All Participants
Male
Female
% Male
% Female

I have tried a number of methods to get this to work. Here is what I have attempted:

  • Dimension Member with Scoped / Direct Assignments
    I manually create a member in the dimension table called % Female and assign the scoped assignment listed below. This produces the correct result in the Sex dimension against any measure accurately and I can filter it without error. The problem is when i review any other dimension the results are fragmented and displayed in decimals even when they normally don't.



SCOPE([Sex].[Sex].[% Female]);
THIS = Divide([Sex].[Sex].[Female],[Sex].[Sex].[All Customers)]);
FORMAT_STRING(THIS)="Percent";
END SCOPE;



  • Calculated Member with Scoped / Direct Assignment

    When I create a calculated member, I see none of the symptoms listed above but I am unable to filter it. Whenever I try to filter it I get an error which states: "A set has been encountered which can not contain calculated members."



CREATE MEMBER CURRENTCUBE.[Sex].[Sex].[All Customers].[% Male]
AS null;

[Sex].[Sex].[All Customers].[% Male] = Divide([Sex].[Sex].[Male], [Sex].[Sex].[All Customers], 0);



  1. Is there any way that I could put these calculated members into a single dimension?

  2. What are the other methods that you guys use to tackle problems like this?


Thank you



124782-image.png


sql-server-analysis-services
image.png (10.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RyanAnderson-3228, we have not get a reply from you. Could the answer from AlexeiStoyanovsky help you? If yes, please do "Accept Answer". If not, please feel free to let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered AlexeiStoyanovsky commented

Hi @RyanAnderson-3228,

Welcome to Microsoft Q&A!

Is there any way that I could put these calculated members into a single dimension?

I think the answer is no. Dimensions are groups of attributes based on columns from tables or views in a data source view. Dimensions exist independent of a cube, can be used in multiple cubes, can be used multiple times in a single cube, and can be linked between Analysis Services instances. Calculated members are kind of measures by combining cube data, arithmetic operators, numbers, and functions. The values of calculated members based on the dimensions/data source, so you cannot put them together. Please refer to Dimensions - Introduction and Create Calculated Members to get more information.


Best regards,
Carrin

If the answer is helpful, please click "[Accept Answer][4]" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Carrin, combining physical and calculated members in a dimension is actually quite possible. In fact, that's what the second link you posted is all about.

0 Votes 0 ·
AlexeiStoyanovsky avatar image
1 Vote"
AlexeiStoyanovsky answered

Both approaches should work, although I don't see the reason in the second approach for first creating the member as null and then doing a value assignment. The error you get for it is actually typical when trying to use calculated members in a scoped assignment.
With the first approach, the values you display, depending on the filtering in the full query, either are outside of the scope and shouldn't be affected, or are inside and should be returned as percentages. Column headers look different for the two cases.
The one problem with such members is that they return less than intuitive results for measures other than sums or counts, but otherwise they shouldn't produce the results you got.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.