I have a fact table with sales figures, a customer table and a product table. And they are set up using the classic star schema where the fact table contains the date, sales, customer ID and product ID. And the dimension tables contain their respective IDs and names. I also created a calculated calendar table which has a relationship with the sales table via the date columns.
I created a simple measure to calculate the YTD sales, something like:
Sales YTD = CALCULATE (
SUM ('Sales'[Sales]),
DATESYTD ('Date'[Date])
)
After that I created a matrix to view the YTD sales for each customer and the products they purchased. In the rows I put the customer at the top and then products at the bottom, and I put the [Sales YTD] measure in the values.
What you would expect to see, is that under each customer only the products that they have purchased would be displayed. However the problem I have is that under each customer EVERY product is being displayed under them. Lets say in the products table I have 100 distinct products and customer A only purchased 5 different products. This matrix is showing all 100 products under Customer A where 95 show $0 and the 5 that he actually purchased show the correct sales amount. And it does this for many of the customers, however a handful of customers correctly display only the products they've purchased (not sure if that gives you a hint as to what my problem is)
I have checked the tables, the relationships, the product and customer IDs and there doesn't seem to be anything wrong with my data model. Other reports I have created in the past with this same data model doesn't have this issue. I don't know if this helps in any way, but the data from the sales model is a direct query from SQL Server but the product and customer table is simply excel data that I copied and pasted via the "Enter data" feature in PowerBI desktop.
Any help or suggestions would be seriously appreciated!
