Sum of rows with different dimension

[TPoismans]

Hi all

I'm running into the following problem:

I got multiple invoices and on each invoice, I can have multiple invoice lines, each representing an article. Now discounts are seen as an article as well, and have a certain code. Consider the following data:

Zone	Customer	Invoice nr.	Discount Code	Amount	Price
XX	A	123	0	2500	750
XX	A	123	105	1	-33.66
YY	B	456	0	1000	685
YY	B	456	105	1	-33.66
ZZ	C	789	0	1000	685
ZZ	C	789	105	1	-33.66

What I eventually want to accomplish, is the following:

Zone	Discount Code	Customer	Invoice nr.	Amount	Price
XX	105	A	123	2500	716.34
XX	105	B	123	1000	651.34
ZZ	105	C	123	1000	651.34

So basically, I want to ignore the dimension [Discount Code] when making my Sum(Amount) AND Sum(Price).

Any help is appreciated.

[sunny_talwar]

Is this something you need to do on the front end or back end? If it is front end, you can try this

Sum({<[Discount Code] = {'0'}>}Amount)

for back end, you can try this

Sum(If([Discount Code] = 0, Amount)) 

[TPoismans]

I need to do this on the front end.

Using that code, the dimension [Discount Code] shifts to 0 as well, while it would still need to show the 105.

The Discount Code '0' means that the article is not a discount. But the invoice can have a discount (Discount Code '105').
I want a table (pivot or straight) where I can see the Region, Discount Code, Customer Nr., Invoice Nr., Sum(Amount) and Sum(Price).

This would tell me on which invoices the discount code 105 was used, the amount they ordered, and the final price they paid, taking the discount into account.

Apologies for not being clear enough.

[sunny_talwar · Accepted solution]

How about this?

If([Discount Code] <> 0, Sum(TOTAL <Zone, Customer, [Invoice nr.]> {<[Discount Code] = {'0'}>}Amount))

[TPoismans]

This seems to work perfectly.

Could you possibly do a quick explanation how the total and set analysis interact here?

[sunny_talwar]

Set analysis restrict to show the  Sum of Amount where Discount Code = 0 and TOTAL <Zone, Customer, [Invoice nr.]>  makes it repeat regardless of Discount Code value in the dimension. and then in order to not see the row with Discount Code = 0, use an if statement.