KPI sum problem

[edemerdjieva]

Hello all,

I have a chart displaying marketing action performance based on two KPI : total of sales and total of discounts.

The Fact table has the following fields:

Store ID	Receipt ID	Item ID	Sales Gross Amount	Sales Net Amount	Discount	Action ID
1	A	111	13.90	8.90	-	-
1	A	201	10.90	8.20	-	-
1	A	201	10.90	8.20	-	-
1	A	151	8.00	6.00	-	-
1	A	111	-	-	5.00	XY
1	A	201	-	-	2.70	XY
1	A	201	-	-	2.70	XY
1	A	151	-	-	2.00	XY

The resulting chart has to be like this:

Action ID	Total Sales	Total Discount
XY	31.30	12.40

The problem is with the Total Sales Net Amount, defined as follows:

=Sum(Aggr(Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))

it takes into account distinct items. So in the example above the result is : 23.10 which is not correct. The good value should be 31.30.

Any idea how to write the expression?

Ce message a été modifié par : Elina Demerdjieva

[OmarBenSalem]

In that case, we alter it as follow:

sum(total <ActionID> Measure)

[Anonymous]

Hello Elina,

Can you try this below expression and let me know whether the expression is working as you expected or not.

Expression : =Sum({<[Receipt ID] = , [Action ID] = ,[Item ID] = >} [Sales Net Amount])

Thanks & Regards,

Venkata Sreekanth

[edemerdjieva]

Hi,

I found the solution. I added a row number to my data. Then the expression worked fine as there was a unique identifier.

Sum(Aggr(Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))

Thanks to all for helping!