ignoring selections in calculated field but still allow filtering

[Shir63]

Hi,

I have a formula to calculate a new column of average %:

sum({<[IS Discount (Y/N)] = {'Y'}> } total <[Sale Order Order Number]> $(vDiscount))
/
sum(total <[Sale Order Order Number] > if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $]))

i have a table where i present a report and when filtering another field, for example "Stream" it changes the % i calculated.

I want for this column that i created, do not consider the "Stream" in the calculation.

using "Stream =" does not help because i still want my table to filter, i just dont want this field to be affected, and change the formula.

example:

list price   stream      avg %   

100               A                 10 %

200                B                 10 %

filtering: A

new wanted table:

list price   stream      avg %   

100               A                 10 %

always show 10%, even if i chose A or B, but also, show me i  am filtered in A/B

(which does not happen with "Stream =", it does not allow filtering at all)

thank you

hope you can help!

[Kushal_Chawda]

try this

sum({<[IS Discount (Y/N)] = {'Y'},Stream> } total <[Sale Order Order Number]> $(vDiscount))
/
sum(total <[Sale Order Order Number] > {<Stream>}if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $])) * avg(1)

[Shir63]

thanks! that's nearly what I was searching.

your answer helped but please see in the picture, it does not really "filter" the data, it keeps the other values under stream only giving it N/A but i would like it to filter, just in this particular field, don't take "Stream" field into consideration

in the picture i am filtered on "Service" under Stream field

[Kushal_Chawda]

forgot to apply aggr try below

sum(aggr(sum({<[IS Discount (Y/N)] = {'Y'},Stream> } total <[Sale Order Order Number]> $(vDiscount))
/
sum(total <[Sale Order Order Number] > {<Stream>}if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $])) ,Stream))* avg(1)

Make sure that in Add-On option to uncheck "Incluce zero Values"

[Shir63]

thank you so much for this help and your time.

this solution worked but caused an issue i didnt have:

adding Aggr caused the Percentage % to appear on  some lines, then on the others its 0

i have created the formula in a way that the avg % would appear on all lines

again, many many thanks for this help.

hope you can help with this last thing

[Kushal_Chawda]

do you want to repeat the % in all rows the try below

sum({<Stream>}aggr(sum({<[IS Discount (Y/N)] = {'Y'},Stream> } total <[Sale Order Order Number]> $(vDiscount))
/
sum(total <[Sale Order Order Number] > {<Stream>}if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $])) ,Stream))* avg(1)

If you want to calculate % for each row then add other dimension of the table in aggr

sum(aggr(sum({<[IS Discount (Y/N)] = {'Y'},Stream> } total <[Sale Order Order Number]> $(vDiscount))
/
sum(total <[Sale Order Order Number] > {<Stream>}if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $])) ,Stream,Dimension1,Dimension2))* avg(1)

[Shir63]

thank you very much again,

i tried both but it does not show the % repeated for each row.

i'm sorry if it wasn't clear 😞

adding a sample it that helps.

i already managed to have the Discount % repeating. with this formula:

sum(total <[Sale Order Order Number]> {<[IS Discount (Y/N)] = {'Y'} >} $(vDiscount))
/
sum(total <[Sale Order Order Number]> if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $]))

order #	line	List Price	is discount	Discount	Modifier	Stream	Discount %
111	1.1	100	Y	10	1	A	0.1375
111	1.2	200	Y	20	1	A	0.1375
111	1.3	250	Y	10	2	B	0.1375
111	1.4	150	Y	30	2	B	0.1375
111	1.5	100	Y	40	3	C	0.1375

now i need, that no matter what i chose in "Stream" - the % will stay 0.1375

and also, if i chose B in stream, then it would present only B and not N/A

does that makes sense?

thanks again! for everything! sorry if this is too much work

[Kushal_Chawda]

Try below. If it does not work then share your sample app which will be helpful to provide quicker answer.

sum(total <[Sale Order Order Number]>aggr(sum(total <[Sale Order Order Number]> {<[IS Discount (Y/N)] = {'Y'} ,Stream>} $(vDiscount))
/
sum(total <[Sale Order Order Number]>{<Stream>} if(IsNull([Discount Name]),[List Price $], [Total Adjusted List Price $])),Stream,[Sale Order Order Number])) *avg(1)

[Shir63]

hi thanks again 🙂

how is best to share an app sample without giving any information regarding company data?

[Kushal_Chawda]

You can create sample excel file with mockup data.