KPI based on Aggr() function

[edemerdjieva]

Hello all,

I have a pivot chart to display number of sales and number of returns of items by business unit, store, and by return reason. One item can be associated with several return reasons.

There are two KPI in the chart: 1) number of item returns and 2) number of item sales. I use Aggr() function to calculate item sales. Below is an example of the chart:

Business unit	Store	Item	Return reason	Nb sales	Nb returns
100	719	A	Broken	23	1
			Change for another item	23	1
			Wrong size	23	1
100	83	A	Wrong size	15	3
			Broken	15	2

The "Nb sales" has to be a total per item. Return reason should not be taken into account in this case.

If item A was sold 23 times in store 719 and returned 3 times then '23' should be displayed on each line (as the example above).

Here is the expression for 'Nb sales':

Sum(Aggr(Sum([Quantity sold]),[Store],[Item]))

The only problem is that value '23' is displayed only one time (on one of the return reason lines). How can it be repeated on each return reason line ?

Thank you in advance,

Elina

[MK_QSL]

Try

Sum(Total <Item,Store> [Quantity sold])

[MK_QSL]

or use

Sum(Aggr(NoDistinct Sum([Quantity sold]),[Store],[Item]))

[edemerdjieva]

Doesn't working. NODISTINCT doubles the result

[oussema_laribi]

Try This :

Sum(Total <"business unit" ,Store,Item> [Quantity sold])

[MK_QSL]

have you tried my 1st answer?

Yeah.. add Business Unit also in Total Qualifier

Sum(Total <Item,Store, [Business Unit]> [Quantity sold])

Sum(Aggr(NoDistinct Sum([Quantity sold]),[Store],[Item],[Business Unit]))

Check spelling of all fields

[edemerdjieva]

It would work if the dimension 'return reason' wasn't included in the chart.

Without the Aggr() function the expression is always = 0.

[edemerdjieva · Accepted solution]

Ok, now it worked. I had to add all dimensions in the chart to the TOTAL qualifier.

Here is the final expression:

Sum(TOTAL <[Business Unit], Store, Item> Aggr(Sum([Quantity sold]),[Business Unit], [Store], [Item]))

Thanks for the help!

[MK_QSL]

Glad that you got your output.

Sum(Aggr(TOTAL <[Business Unit], Store, Item> Sum([Quantity sold]),[Business Unit], [Store], [Item]))

Have you tried this?