Sum Total with multiple conditions

[arvinserco]

Hi all,

Row Labels	Negative	Positive
AA	-2400792.828	567974.053
BB	-318395.583	24996.186
CC	-7764599509	176429.394
Grand Total	-7767318697	769399.633

I need to calculate the percentage of each "row labels" out of Grand Total  for both Negative & Positive Category. Also I need to use the filter as "Class1" from one of the column's (by using which I get the above Data table). I need to put all these conditions in one expression.

If anyone can help let me know.

[sunny_talwar]

What is your expression right now for Negative and Positive? Also, what is the expected output? Can you share that as well

[arvinserco]

Output should be this:
Row Labels Negative Positive
AA 0.031% 73.820%
BB 0.004% 3.249%
CC 99.965% 22.931%

Expression used as of now :
Sum(sales)/Sum(TOTAL Sales)
Note: Negative & Positive, Row labels are in two different columns in data and I need it in above format.. By using above calculation as measure and other fields as dimensions.

[sunny_talwar]

---

@arvinserco wrote:
Note: Negative & Positive, Row labels are in two different columns in data

---

What do you mean? do you have two fields called? Negative and Positive or are they part of a single field?

[arvinserco]

they are part of single field.....

[arvinserco]

It is something like this:

Row Labels	Margin	Sales
AA	Negative	-2400792.828
AA	Positive	567974.053
BB	Negative	-318395.583
BB	Positive	24996.186
CC	Negative	-7764599509
CC	Positive	176429.394
Grand Total		-7766549298

[sunny_talwar]

Try this

Sum(sales)/Sum(TOTAL <Margin> Sales)

[arvinserco]

Thanks Sunny,

I got the expression now:
For Positive: Sum({<Margin={'Positive'}>}sales)/Sum(TOTAL <Margin>Sales)
For Negative: Sum({<Margin={'Negative'}>}sales)/Sum(TOTAL <Margin>Sales)
And if you can help me with one more condition if I have to use a filter from one of the fields namely.... if Filter = "Class1" in the expression.

Then this whole things covers up.

Thanks 🙂

[sunny_talwar]

I am not sure I follow?

[arvinserco]

@ sunny not a problem.... See below data.

Region Row Labels Margin Sales
Class1 AA Negative -2400792.828
Class1 AA Positive 567974.053
Class1 BB Negative -318395.583
Class1 BB Positive 24996.186
Class1 CC Negative -7764599509
Class1 CC Positive 176429.394
Grand Total -7766549298

There is one more column "Region" in which there are different regions but I need to filter only "Class1" region. How should I use this filter in above expression.
For Positive: Sum({<Margin={'Positive'}>}sales)/Sum(TOTAL <Margin>Sales)
For Negative: Sum({<Margin={'Negative'}>}sales)/Sum(TOTAL <Margin>Sales)

NOTE: I got the answer but I need to use a filter extension and choose "Class1". All i want to use this filter in calculation itself. I hope you got my point