Hello,
i'm trying to get the percentage of subtotals in sales report.
it's categorised by Department.
and I added query calculation : [Sales] / total( [Sales] for [Department] ) for [% of Sales]
for the first category, it works fine.
but, i don't have any idea why it doesn't work from the second category.
it has 100% from the second to the end category.
Department Department No. Sale s % of Sales
A 10 100 33% => 100/300
A 10 200 67% => 200/300
B 20 300 100%
B 20 300 100%
please help me out in this situation.
Thanks in advance.
to be more detailed.
it's working only with department.
but, with department no.
it's not working...
it shows the individual sales for total.
anyone have any suggestions?
are you using a list?
try to group the department no. then set department group span property = department no
(you can also do it the other way around)
thanks for the reply,
i'm using list in DMR.
I set up as you explained, but the result is
Department Department No. Sale s % of Sales
A -- 100 33% => 100/300
A -- 200 67% => 200/300
B -- 300 100%
B -- 300 100%
Department No. is disappeared.
and the others are the same..
please guide me if you have any other suggestion.
Thank you again.
Quote from: khayman on 27 Jan 2015 10:13:31 PM
are you using a list?
try to group the department no. then set department group span property = department no
(you can also do it the other way around)
hmm i sense something not quite right.
i never heard or experienced a list returning '--'. i know crosstabs do. anyway...
is Department and Department No in the same Level? Department is the member caption and Department No is the business key?
yes, they are at the same level.
and as you said, the key is dept. no.
I tried to set department group span property = department no
and vice versa, but the result was the same as I did for the first time. :(
maybe it's because they are at the same level which makes the for clause at total function incorrect?
I'm still digging.
Thanks
strange. if dept no is the key, your sales for Department A should be 300. it will not be segregated into 100 and 200.
was any default property changed in the data items, list or query? Like for query the Auto Group and Summarize might have been changed.
because, ordinarily, by default, the list will summarize your Department, Department No and Sales to
Department Department No Sales
A 10 300
B 20 300
you're right.
I didn't explain enough.
the key was dept. no. and it was binded w/ dept.
the number that was differentiated was the product group number.
so, dept. - dept. no. - product group - product group number.
dept. and dept.no, product group and product group number are at the same level for each binding.
anyhow,
I found the way to get the subtotal in the group by applying
aggregate( [measure] for group ) instead of total function.
I really appreciate your help.
your reply led me to try everything I can.
G'day.