Customer count and exception as per customer product portfolio
## Problem Description I'm trying to create a measure that counts customers with no sales or with sales based on "Customer_portfolio" . However, my current measure isn't producing the expected results when used in a visual with these attributes. ## Data Model I have three main tables: 1. **Customer_Portfolio** - Customer Code - Product Code 2. **Customer_Master** - Customer Code - Depot - Route - Area - Customer Type - Classification 3. **Sales fact table** - Customer Code - Product Code - Sales Date - Sales Amount Relationships: - Customer_Portfolio[Customer Code] -> Customer_Master[Customer Code] - Customer_Portfolio[Product Code] -> Product Master [Product Code] - Sales fact table[Customer Code] -> Customer_Master[Customer Code] - Sales fact table[Product Code] -> Product Master [Product Code] ## Current Measure Customers with Zero Gross Sales = CALCULATE( COUNTROWS( FILTER( ADDCOLUMNS( VALUES(Customer_Portfolio[Customer Code]), "GS", CALCULATE([Gross Sales]) ), [GS] = 0 ) ), ALLEXCEPT(Customer_Master) ) ``` ## Gross Sales Measure Gross Sales = SUM(Sales fact table[Sales Amount]) ## Expected Behavior When I use this measure in a table visual with attributes from the Customer_Master table (like Depot, Route, etc.), I expect to see the count of customers with zero sales for each category of the selected attribute. ## Actual Behavior The measure doesn't show values against each attribute category as expected. It's not clear if the measure is calculating correctly or if there's an issue with how it's interacting with the visuals. ## Sample Data Here's some sample data to illustrate the problem: **Customer_Portfolio** | Customer Code | Product Code | |---------------|--------------| | C001 | P001 | | C001 | P002 | | C002 | P001 | | C003 | P003 | **Customer_Master** | Customer Code | Depot | Route | Customer Type | |---------------|-------|-------|---------------| | C001 | D1 | R1 | Retail | | C002 | D1 | R2 | Wholesale | | C003 | D2 | R3 | Retail | **Sales fact table** | Customer Code | Product Code | Sales Amount | |---------------|--------------|--------------| | C001 | P001 | 100 | | C001 | P002 | 0 | | C002 | P001 | 0 | | C003 | P003 | 0 | ## Questions 1. Why isn't my measure showing values for each attribute category? 2. How can I modify the measure to correctly count customers with zero sales by different attributes? 3. Is there a better way to structure this measure or my data model to achieve the desired result? Any help or guidance would be greatly appreciated!