none
Possible to use measures in calculated column formulae?

    Question

  • Hi all,

    In particular, I want a roll-up-able column displaying Percentage Revenue Of Total.

    I have a measure for total revenue, and my thought was to add a calculated column that divides the row's revenue by that measure.

    Will this work? If not, what an appropriate way to do this?

    thanks for any suggestions,

    sff

    Wednesday, April 03, 2013 10:38 PM

Answers

  • You can create a calculated measure that overrides the current dimension member.  For example,

    with member Measures.EnterpriseRevenue as (Measures.TotalRevenue, root([Customer]), root([Product]), root([Store]))

            member Measures.PercentageEnterpriseRevenue as Measures.TotalRevenue/Measures.EnterpriseRevenue, format_string="#,#.00%"

    Think about how you might look at time.  For example, if a user is looking at 2013, then you might not want to override this to get the total for all time.  But that's up to you.  Also, you might want to cater for division by zero to return null when EnterpriseRevenue is 0.

    http://RichardLees.blogspot.com 


    Richard

    Wednesday, April 03, 2013 11:19 PM