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

# Calculated Field to get total of a measure

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.

• ###### 1. Re: Calculated Field to get total of a measure

Hi Andrea,

Create a calculated field.

~Tushar

• ###### 2. Re: Calculated Field to get total of a measure

Hi Tushar, thank you for your time and kindness. Unfortunatly I often need to see/filter just one brand.

If I use the WINDOW_SUM I obtain the same value of Sales, but I need 13.