Table Box or in Pivot Table

[marishnagendran]

Hi All,

Product Name	Customer Name	Qty	Net Profit
Total		265	98
Product A	Customer A	10	2
	Customer B	5	2
	Customer C	15	5
	Customer D	20	7
Product B	Customer E	10	2
	Customer A	24	5
	Customer B	16	4
	Customer F	50	12
	Customer Z	20	5
Product C	Customer Q	40	20
	Customer A	20	2
	Customer B	35	32

This is my above transaction table.

I want to calculate Every product Average Net Profit based on above transaction table (i.e)

Item Group	Avg_Net Profit
Product A	4
Product B	5.6
Product C	18

Once calculated i want to compare with each product average with transaction table.

If value is less than the average i want to take that transaction (i.e)

My Output

Product Name	Customer Name	Qty	Net Profit
Product A	Customer A	10	2
	Customer B	5	2
Product B	Customer E	10	2
	Customer A	24	5
	Customer B	16	4
	Customer Z	20	5
Product C	Customer A	20	2

[marishnagendran]

Is it possible or not

[Edvin]

You could try something like this:

=IF(aggr(avg([Net Profit]),[Product name])<[Net Profit],[Product Name])

[marishnagendran]

Thanks for your reply..

It's not working edvin

[lanlizgu]

Straight Table with Product Name and Customer Name as dimensions and the following expressions:

if([Net Profit]<
(sum(total <[Product Name]>[Net Profit])
/
count(total <[Product Name]> [Customer Name])
)
,[Net Profit]
,null()
)

if([Net Profit]<
(sum(total <[Product Name]>[Net Profit])
/
count(total <[Product Name]> [Customer Name])
)
,Qty
,null()
)

[marishnagendran]

It's not working

[lanlizgu]

It works perfectly. PFA a .qvf attached

[marishnagendran]

I think my net profit column is calculated field that's why it shows syntax error

[lanlizgu]

could you please share your data so I could check?

[marishnagendran]

Net Profit Column is based on Expression

((Sum(kpi_MR)/100000 )

/

(Sum(Kpi_SaleValue)/100000) * 100