4 Replies Latest reply on Aug 6, 2018 11:13 AM by Deepak Rai

    Aggregated Calculations within a Dimension - v10.2

    Gustav Tollemar

      Im looking to do an aggregated calculation on two attributes within a single dimension. Lets say all my measure categories (ei. sales, contribution margin, fixed costs) are in a dimension called KPI with my only measure being "value". Im trying to generated a second calculated value that takes the sum of fixed costs / sum of sales for an level of aggregation i choose using alternative dimensions. Due to the confidentiality of my data I've attached a twbx of condensed superstore data. To illustrate my question please refer to "example 1".

       

      I would like a calculation that will give me the ratio of furnishing sales to phone sales contingent on my level of detail with dimensions. For screenshot below I want 2014 to for example say 3.1% which would be 68/2200. It would of course calculate a % for 2015-2017 as well.

       

       

      2018-08-06_12-29-00.png

       

       

      The calculation would have to change with changes to my level of detail, for example if i filter on only California in the state field i would want that same number in 2014 to read 2.4%.(49/2032).

       

      2018-08-06_12-40-58.png

       

      The only way i can come up with to getting a solution is to create a separate measure for each KPI, but ideally would like to stay away from this as the data will always come to me in the format im working with.