Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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
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"
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
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)
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
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)
hi thanks again 🙂
how is best to share an app sample without giving any information regarding company data?
You can create sample excel file with mockup data.