Help with Set Analysis

[#1]

Hello I have a table "Sales' that I am filtering using 'Set Analysis' and I would like to add another column that shows me the Total % per line. I know how to do this with when using simple operations and no restrictions on the data but I don't know how to get there when restricting the results. For example on my table "how to Replicate this' I have 2 columns: Sales and %, where sales =Sum(QTY) and %=Sum(QTY)/Sum(TOTAL<Year>QTY). Because my 'Sales' table has restrictions on the data I am unable to show the %; I tried using the Column Label but I couldn't get it to work: $(='[Sales])/Sum(TOTAL<Year>$(='[Sales])). Can anybody help please?

I am attaching a sample qvw file.

Thank you,

Hugo

[stigchel]

See attached your example edited

[JonnyPoole · Accepted solution]

Is this what you are looking for ?

i wasn't sure what you wanted to do with the Code field... it has a set modifier which prevents seeing Code=1.

I removed it for the example on the top left and incldued it on the right

hopefully one of them is what you are looking for.

ps:  just use TOTAL in the denominator and not in the top.

[#4]

Thank you Piet and Jonathan for your help, both answers are very helpful but it was Jonathan's answer the one that gave me the 100% correct answer, because in the case when filtering out records, the % must reflect this on the results and by placing the entire expression after the TOTAL, the result is adjusted accordingly.

I really appreciate your help, I tried different ways for about 20 hours and I couldn't get it to work.