How to create average for Measures

[Anonymous]

Data Model Scenario:

 - Dimension Product Table which contains Product ID, Product Name, Dept Name, and Division Name

 - Fact Table which contains Transaction ID, Product ID

Problem:

I created three measures for each product count, and relevant dept and division count. Now, I want to create a measure that compares the product count against average dept count and division count. For example, Dept 1 has 5 types of products and their total count is 35 which makes an average of 7 (35/5) and Division 1 has 15 products and their total count is 60 which makes an average of 4 (60/15).

Would highly appreciate any guidance on how to create measure(s) which compares a Product Count against Average Dept Count and Average Division Count?

[sturlaws]

Hi @Anonymous,

not sure if I understand what you are after, your naming conventions are a bit confusing.

Could you try this and see if it gives you what you need:

average pr department =
DIVIDE (
    [Dep count];
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Product name] );
        ALL ( 'Table'[Product name] )
    )
)