1 Reply Latest reply: Nov 16, 2016 4:32 PM by Liron Baram RSS

    A ratio as a calculated dimension

    Halmar Heijnen

      Hi all,

       

      I have a lot of data, Costs and revenues. Together they make margin. What I want is to make a barchart with in the X-axes a categorised margin and in the Y-axis the number of contracts. it is like this:

       

      Knipsel.PNG

      My epression for the margin is like this:

      (SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

      - SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))

      /SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

       

      The expression for the number of contracts is just:

      COUNT({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}DISTINCT [ContractNo])


      Can anyone bring me in the right direction??


      Thanks,


      Halmar

        • Re: A ratio as a calculated dimension
          Liron Baram

          you need to create a calculated dimension

          the will go something like this

          num(subfield(class(aggr(

          (SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

          - SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))

          /SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue]),[ContractNo]),0.1),'<',1),'#,##0%')

           

          and use as expression your expression for counting contracts from above