Counting average on specific sum

[#1]

Hi,

I have a formula problem with an average calculation I am trying to set up. I have an app where I show different sales in different categories for different people using the following formula:

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])

This formula works fine for its calculation, however when I try an rewrite it to an average calculation i cant seem to get it to work. This is the formula I have tried:

Count({$<[Sales Category]={"Shoes and other"}>})/Count(Sales Person)

but I cannot seem to get it to work. Help plz.

Cheers,

[#2]

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales])/COUNT(TOTAL [Sales Person])

Sales mean your field where sales value...

[#3]

Im sorry but this is not again accounting for the sales category "Shoes and other", or am I missing something. It doesnt work anyhow, it results in a "1" which isnt correct

[MK_QSL]

Looks like we are unable to understand your requirements !!

Can you be more specific in your clarification ? We need to understand first ....

Thanks....

[#5]

I think below you want...

You want to count sales category where shoes and other

Divided by total sale person

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales Category])/COUNT(TOTAL [Sales Person])

Vinay

[#6]

ok,

i want to take the total sales for the category "shoes" and divide that by the number of sales people i have. so i need the system to go into the database and look in the column "sales category" find "shoes" and take all the sales for that category and sum it up. then divide that by the total number of sales people.

this is why i need some kind of ID like "sales category" = "shoes" otherwise it will not work.

i hope this clarifies everything.

[SunilChauhan]

try this

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])/Count({<[Sales Category]={*}>}[Sales Person])

it would be great if attached sample with explaination

[MK_QSL]

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count(Distinct [Sales Person])

or

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count(TOTAL [Sales Person])

or

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count({1} [Sales Person])

[sunilkumarqv]

Try this

Hope this gives what you need actually

= Avg( Aggr( count({<Sales Category={"Shoes and other"}>}),Sales Person ))

[#10 · Accepted solution]

Hi all,

I solved this after several different tries:

(Count({$<[Sales Category]={"Shoes and other"}>} [Sales]) / Count (distinct [Sales Person]))

Thank you all fro your input!

Cheers,