Post reply

Common calculated measure across all (any) dimensions

  • April 9, 2015 at 5:49 am

    #145669

    I have a cube with 4 dimensions and I have a measure called `Transaction Count`. Now I want to calculate the `Percentage` across all the dimensions for that above measure.

    I also have a dimension called `Cars`. I have the count across all the `Cars` and now I have defined a calculated measure for calculating the `Percentage` of each car from the total number of transactions. But it will work only for that particular dimension.

    How I can create a single percentage calculated measure which can be used across all the dimensions?

    MDX for the calculated measure: (which is working for only Carmake dimension)

    CASE

    WHEN ISEMPTY( [MEASURES].[Trans COUNT] )

    THEN 0

    ELSE ([Dim Car Make].[Hierarchy].CURRENTMEMBER,

    [MEASURES].[FACT COLORPERFORMANCE COUNT])/

    ( [DIM CAR MAKE].[CARMAKE].[(ALL)].[ALL],

    [MEASURES].[Trans COUNT])

    END

    I already have a Trancount(1000) measure. Now I need to create a calculated measure Freq % which should be calculated across all the dimensions.

    Table 1 in screenshot - you drag and drop the carmake dimension, then both Tran Count and Freq% should be calculated as per CarMake breakdown.

    Table 2 in screenshot - you remove CarMake and drag Quality, then both Tran Count and Freq% should be calculated as per Quality breakdown.

    Table 3 in screenshot - you remove Quality and drag Brand, then both Tran Count and Freq% should be calculated as per brand breakdown.

    [1]: https://www.sqlservercentral/Forums/Uploads/Images/1675731-1.jpg

  • April 9, 2015 at 5:59 am

    #1789825

    You could achieve this using the Axis function in MDX. Here is an example in AdventureWorks:

    WITH MEMBER Measures.PercentOfAxis AS

    [Measures].[Internet Sales Amount]

    /

    SUM(AXIS(1), [Measures].[Internet Sales Amount]), FORMAT_STRING="Percent"

    SELECT {

    [Measures].[Internet Sales Amount]

    ,Measures.PercentOfAxis

    }

    ON 0,

    {[Date].[Calendar Year].AllMembers} ON 1

    FROM [Adventure Works]

    In this example the Axis(1) value happens to be AllMembers of Calendar year so a percentage is calculated per row for each year. Whatever you put on the 1 Axis will be calculated in the same way (provided the dimension is connected to the Internet Sales Amount measure).

    I hope this helps.

    I'm on LinkedIn

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply