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!
Plotting a Graph
hi everyone - Objective: Apply the total cost for each month that the project is active. For example, Project A: Jan-2020: $1000 Feb-2020: $1000 Mar-2020: $1000 Apr-2020: $0 Project Cost Start Date End Date A $1,000 1/1/2020 3/31/2020 B $5,000 1/1/2021 5/5/2021 C $6,000 3/3/2020 3/5/2020
distribution of calculated measure
Hi all, i dont even know if the subject title fits to what i am actually looking for, but i will try to explain here more in details. i have model contains 4 different tables/databases: calendar table, products list, customers list and sales table of customers and products bought by them monthly. i already have measures that: 1. counts num. of products 2. counts num. of customers I wish to present a clustered column chart which counts how many customers have bought each number of products from 1 to max products i have. for example, lets say i have 10 products: i wish to get the above result. thanks in advance, Amit
