0

I have two tables in my data model. You can call them Customer and Order and they are related by a key CustomerID, so the model could look like this: Customer: CustomerID CustomerName

Order: OrderID CustomerID OrderTotal

There is a relationship created based on CustomerID. Scenario: I create a PivotTable displaying CustomerName from the Customer table in Rows. Then use OrderTotal as an SUM aggregate in Values. The aggregation works fine until you have duplicate values in CustomerName. For example, customer1 is CustomerID 1, CustomerName John Smith. Second customer is CustomerID 2, CustomerName John Smith. The result is only one John Smith is listed in the PivotTable and all of the OrderTotal values are aggregated for all John Smith's. I don't understand this behavior. Can someone explain this and ultimately a way around it? I would expect to see two John Smith rows with an aggregation matching the relationship key (CustomerID), not CustomerName.

vcraigc
  • 91
  • 1
  • 8

1 Answers1

0

You will need to change your CustomerName column to append the Customer ID (at least on customers who have the same name as someone else). Then the two John Smiths will be kept separate. If you are able to do this upstream in your SQL query that's great. If not and you need help with a DAX calculated column for this let me know.

Other solutions would rely on the PivotTable user to add CustomerID to the PivotTable to force separating John Smith by ID.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Would these options result in the ID being visible to the user? – vcraigc Apr 08 '16 at 10:59
  • @user1644708 yes. Unless you can come up with an alternative naming scheme that is unique without the ID). Maybe John Smith (Customer since 12-2015) or something? – GregGalloway Apr 08 '16 at 11:33