2 Replies Latest reply on Apr 18, 2018 8:19 AM by Andrea Grella

    Calculated Field to get total of a measure

    Andrea Grella

      Hi community!

       

      Let’s suppose this is my dataset:

       

      Brand

      Country

      Sales

      A

      IT

      6

      A

      FR

      5

      A

      DE

      3

      B

      IT

      7

      B

      FR

      4

      B

      ES

      1

       

      I would need a calculated field that shows me this value (in red):

       

      Brand

      Sales Value

      Sales tot

      A

      14

      26

      B

      12

      26

       

      This would help me to compute the percentage of sale made by A over total: 14/26

      Let’s suppose then that I want to filter by Country.

       

      If I filter by “IT” I expect this result:

       

      Brand

      Sales Value

      Sales tot

      A

      6

      13

      B

      7

      13

       

      This would help me to compute the percentage of sales made by A in Italy over total in Italy: 6/13

      How can I obtain such calculated field?

      I tried with {FIXED Country : Sum(Sales)} that works well when I filter by “IT”, but that does not work when I do not use the filter. This is the result I obtain:

       

      Brand

      Sales Value

      Fixed

      A

      14

      25

      B

      12

      23

       

      It happens because there is no row containing sales in ES by brand A neither sales in DE by brand B.

      A “workaround” is to transform underlying data in this way:

      Brand

      Country

      Sales

      A

      IT

      6

      A

      FR

      5

      A

      DE

      3

      A

      SP

      0

      B

      IT

      7

      B

      FR

      4

      B

      SP

      1

      B

      DE

      0

       

      Obviously, I am simplifying. My dataset is much larger and I have to compute such percentages over different dimensions. The “workaround” is working, but the number of rows are growing up day by day.  I cannot go on like this and I really need something different to solve this issue.