Calculated expression on a chart

[Dwyane]

Hi All,

Im having some trouble with a table that i wondering if i could get some help on. I have a table that i would like to look like this

Month	CUSTOMER_ NAME	DOB	REF	Price	Sold	Actual%
1	ABC	2020-11-23	ABC123	100	300	33%
1	DEF	2020-03-01	DEF123	150	312	48%
1	GEH	2020-01-01	GEH123	200	324	62%
2	ABC	2020-11-23	ABC123	250	336	74%
2	GEH	2020-01-01	GEH123	300	348	86%
2	DEF	2020-03-01	DEF123	350	360	97%
3	ABC	2020-11-23	ABC123	400	372	108%
3	DEF	2020-03-01	DEF123	450	384	117%
3	GEH	2020-01-01	GEH123	500	396	126%

However unless i click on a particular customer name, or customer reference the actual does no populate  

Price is sum(aggr(sum(X_Price),[Month]))

Sold is sum([Sold_Price])

Actual is sum([Sold_Price])/sum(aggr(sum(X_Price),[Month])),[CUSTOMER_ NAME],[Ref], [DOB]))

My dimensions are CUSTOMER_ NAME, REF and DOB. Im not what i should change so the actual value populates without selecting a particular customer

Thanks in advance!

[stevejoyce]

i'm not sure why aggr is needed as opposed to Total <field>.  But if you use aggr, it's only going to return 1 row for each field in your aggr.. i.e. 1 row for each Month, so that's probably your issue.  Try adding NODISTINCT like:  sum(aggr(NODISTINCT sum(X_Price),[Month]))

Carry this to Actual as well.