Aggr Advice Required

[gavin_kite]

Hi

I'm looking for some help on Aggr. I thought i had a pretty good knowledge of Aggr but this has stumped me!

I have total sales by MM - YY and I have discounts, as you can see from the table below, apart from the date, the 2 numbers aren't related in the data model

Current Position

Month Year	Discount Type	Discount Amount	Total Sales	Discount %
Jan 2017	-	-	10,000	-
Jan 2017	Coupon	500	-	-
Jan 2017	Staff	1000	-	-
Jan 2017	Till Discount	200	-	-
Feb 2017	-	-	20000	-
Feb 2017	Coupon	300	-
Feb 2017	Staff	600	-
Feb 2017	Till Discount	500	-	-

What I'd like to do is to work out at discount type level, what percentage of discount has been given as a percentage of total sales. Feels like this should be achievable with Aggr but I'm not having much luck

Required Solution

Month Year	Discount Type	Discount Amount	Total Sales	Discount %
Jan 2017	-	-	10,000	-
Jan 2017	Coupon	500	10,000	5%
Jan 2017	Staff	1000	10,000	10%
Jan 2017	Till Discount	200	10,000	2%
Feb 2017	-	-	20,000	-
Feb 2017	Coupon	300	20,000	2%
Feb 2017	Staff	600	20,000	3%
Feb 2017	Till Discount	500	20,000	3%

My attempt so far has been

Exp 1

Sum ([Discount Amount])

/

Sum(Aggr(Sum([Total Sales]),[Month Year])

Exp 2

Sum ([Discount Amount])

/

Sum(Total Aggr(Sum([Total Sales]),[Month Year])

Can anyone help me out?

Thanks

[Anonymous · Accepted solution]

use aggr(nodistinct ...)

[gavin_kite]

Thanks for the reply

This did the trick

Sum ([Discount Amount])

/

Aggr(NoDistinct Sum([Total Sales]),[Month Year])