Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to create average for Measures

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?

 

 

average
Measures

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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] )
    )
)