putting a formula together correctly for average retail

[Anonymous]

im having issues putting the formula together correctly

I need average retail which is

((totalretail*totalsales)/totalsales)

below are the 2 formulas for both.. but I cant get them together in qv correctly

can someone help?

total retail formula

=sum(aggr(sum(DISTINCT [2retail]),[style])) 

total sales formula

=sum({<[2bfasefg],[2bc]>} sales)

thanks

[sunny_talwar · Accepted solution]

This isn't working?

(Sum(Aggr(Sum(DISTINCT [2retail]), [style])) * Sum({<[2bfasefg],[2bc]>} sales))/Sum({<[2bfasefg],[2bc]>} sales)

or

Sum(Aggr((Sum(DISTINCT [2retail]) * Sum({<[2bfasefg],[2bc]>} sales))/Sum({<[2bfasefg],[2bc]>} sales), [style]))

[Anonymous]

the 2nd one gives me the more correct answer...

when I isolate to a body group.. both formulas are correct

when I show all bodies.. the first is incorrect whereas the 2nd one is correct

not sure why

[Anonymous]

there is something I just realized I screwed up and not sure how to do this..

I forgot color..

basically..  its

I think its whats wrong with my thing,,,

right now if im right

its    distinct (style) retail* all sales by fabgrp+bodytype/ all sales by fabgrp+bodytype

but I think I need

(distinct(style)retail * all sales for style/color)/all sales by fabgrp+bodytype

I created a new field called stylecolor and did that for sales.. but it doesn't seem to work

attached is the newest version

[sunny_talwar]

Attached? I don't see anything attached?

[Anonymous]

whoops

thought I did

I figured out the real way of doing the total for wholesale and sales

but the issue is still with average.. I thought I did it right keeps giving me error for that

also the percent to total seems to be off too and I cant figure out that yet

thanks

[sunny_talwar]

What are the numbers you are expecting to see? Also, your expression for Avg Retail is not right

Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2retail]), [style])),[style], [stylecol]))/Sum(Aggr(Sum({<[style],[stylecol]>} sales)))

In the denominator, you use Sum(Aggr(..., but you don't specify any dimension. Either don't use Sum(Aggr(...

Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2retail]), [style])),[style], [stylecol]))/Sum({<[style],[stylecol]>} sales)

or specify the dimension.....

[Anonymous]

hi sunny

sorry I forgot I had one more fabric group below.. the percent to total is right

for the other

should the denominator be  Sum({<[style],[stylecol]>} sales)

in the formula..  the front part before the / is the top. and the other part after is the denominator?

why would it try to sum(aggr across the division /?

Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2retail]), [style])),[style], [stylecol]))/Sum({<[style],[stylecol]>} sales)

[sunny_talwar]

jfung148‌ -

I am completely confused here, can you just let me know what the numerical output you are looking to get?

[Anonymous]

hi

I finally figured out what u meant about the denominator being aggr wrong..    that messed me up why the multiplier is in the middle of the aggr.. but I fixed that  (thanks)

if u see percent to total all those numbers add to 100 only on anything with sales..

but the percent to total wholesales isn't doing percentage only with anything with sales.. its doing it for all..

I think im finally seeing the aggr formulas correctly