Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
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] ) ) )
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |