Aggregation expression difficulties

[Brunommsilva]

Hello, 

Been struggling to replicate an expression that the costumer needs. Excel attached. 

I need an expression to get the YTD value (which is the 87% in the orange cell) on a Pivot table where none of the relevant fields exist as Dimension except for Name and Product, which can be collapsed. I've tried innumerous combinations of Aggr expressions, but  nothing seems to be working. 

I would be very grateful if someone has a solution for this. 

The Excel is attached. I'm not pasting the data here as it would be confusing to read. 

Thanks in advanced!.

Bruno. 

[justISO · Accepted solution]

Hi, it depends on how you get this 'example' data table, but from it you can get this 87,3% with formula:

sum(aggr( ((Sum([SALES MONTHLY])/Sum([TARGETS MONTHLY]))*[PROD WEIGHT])*(count([PROD WEIGHT]) / 7) ,[PRODUCT NAME],[PROD WEIGHT]))

where I'm not sure what this 'tYTD=7' means, so I added it by hand, but it can be replaced with measure, like count(total distinct DATAMONTH) or something else.

[Brunommsilva]

Hi, Thank you very much. 

You don't even imagine how many variations of this expression I tried. I was very close to it, but I guess I started running in circles. 

Thanks once again! 

Best Regards,

Bruno Silva.