Average of Measures

[Anonymous]

Hi, I have three calculated measures, Product Count which counts products, Dept Count which counts total products in a dept and Division Count which counts total products in the division. For example, Dept 1 has 5 different products with a total count of 35 which gives an average of 7 (35/7) and Division 1 has 15 different products with a total count of 60 which gives an average of 4 (60/15).

I want to calculate measures for average Dept count and Division count so that I can each product's product count against average Dept count and average Division count. Any help and guidance would be highly appreciated.

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Try this, where 'Table' is the name of your table.

Dept Average = DIVIDE('Table'[Division Count] ,DISTINCTCOUNT('Table'[Dept Name]))

If this answers your issue, please mark this as the solution. Kudos are nice too!

Nathaniel

[Icey · Accepted solution]

Hi @Anonymous ,

Do ‘7’ and ‘4’ what you said above are part of the results of your measures ‘average Dept count’ and ‘average Division count’?

And are your three calculated measures created like so:

Product Count =
COUNT ( 'Table 1'[Product Name] ) + 0

Dept Count =
CALCULATE (
    COUNT ( 'Table 1'[Product Name] ) + 0,
    ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] )
)

Division Count =
CALCULATE (
    COUNT ( 'Table 1'[Product Name] ),
    ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] )
)

‘Table 1’ is the specific value table, for example, sales. And ‘Table 2’ is the specific product category table.

If so, you can create your measures ‘average Dept count’ and ‘average Division count’ like below:

Different product count of every Dept =
CALCULATE (
    DISTINCTCOUNT ( 'Table 2'[Product Name] ),
    ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] )
)

Different product count of every Division =
CALCULATE (
    COUNT ( 'Table 2'[Product Name] ),
    ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] )
)

average Dept count = 
DIVIDE ( [Dept Count], [Different product count of every Dept], 0 )

average Division count = 
DIVIDE([Division Count],[Different product count of every Division],0)

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey  and @Nathaniel_C , one more request, please!

How can I eliminate products from the distinct count in Dept and Division where product count is 0. For example,  In Dept 1, DP 15 has a zero count, so distinct count of products for Dept 1 shall be 4 instead of 5.

I tried IF(Not(IsBlank(Product Count) but it does not give the correct answer.

Thanks,

[Icey]

Hi @Anonymous ,

You can change the measures like so:

Different product count of every Dept 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[Product Name] ) + 0,
    ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] )
)


Different product count of every Division 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[Product Name] ) + 0,
    ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] )
)


average Dept count 2 =
DIVIDE ( [Dept Count], [Different product count of every Dept 2], 0 )


average Division count 2 =
DIVIDE ( [Division Count], [Different product count of every Division 2], 0 )

Here is my PBIX file. If you need, you can download it.

Best Regards,

Icey

[Anonymous]

Hi @Icey , thanks for ther response. The new measure still count Product Name with zero Product Count. In Dept 1, four products have Product Count >0 and I want to count them only in my distinct count in Different Product Count of every Dept. The logic that I want to build is to create a flag for active and inactive product, and count only active products.

Thanks again for your support.

[Anonymous]

Thanks, @Icey, and @Nathaniel_C  for your time and support. I was looking for a solution similar to @Icey  and it really helped.

[Nathaniel_C]

Hi @Anonymous ,

If I understand what you are looking for, in the data shown, the average dept count would be (35+10+0+15)/4 or 15 and the average division count would be 60/1?

Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Try this, where 'Table' is the name of your table.

Dept Average = DIVIDE('Table'[Division Count] ,DISTINCTCOUNT('Table'[Dept Name]))

If this answers your issue, please mark this as the solution. Kudos are nice too!

Nathaniel