I created two tables to replicate the issue I'm experiencing. I did find one post using a DAX fomula solution but was unable to recreate it to resolve my issue.
Here are the table I am trying to relate. I have Created the relationship in the Diagram View from the "BenchmarkData" table to the "VendorDemo" table across the "FruitName" field, but it changes the direction each time.
As such when I try to get values from "BenchmarkData" it kicks back the sum of the field...
Unique ID VendorNumber VendorName DelveryDays Manager FruitName 1 Peter Peter's Produce Mon Kevin Lemons 2 Paul Paul's Produce Tue Kevin Watermellon 3 Mary Mary's Produce Tue Kevin Bananas 4 Sam Sam's Produce Mon Kevin Plums 5 Dave Dave's Produce Tue Scott Apples 6 Larry Larry's Produce Tue Scott Oranges 7 Brenda Brenda's Produce Tue Scott Bananas 8 Archie Archie's Produce Tue Scott Watermellon 9 Rocky Rocky's Produce Mon Kevin Lemons 10 Allan Allan's Produce Mon Scott Bananas
Benchmark ID FruitName Days for Delivery Shelf Life Days to Expiration Expiration Percentage 1 Apples 5 25 20 0.2 2 Bananas 6 15 9 0.2 3 Oranges 5 32 27 0.2 4 Plums 6 15 9 0.2 5 Watermellon 5 25 20 0.2 6 Lemons 5 19 14 0.2
VendorName FruitName Sum of Days to Expiration Allan's Produce Bananas 99 Archie's Produce Watermellon 99 Art's Produce Bananas 99 Bart's Produce Oranges 99 Brandi's Produce Plums 99 Brenda's Produce Bananas
Thanks for any help. New to PowerPivot, but I did try to find a solution, Relationship Tutorial, etc. before posting.
Relationships in PowerPivot are used to lookup from the many side (your VendorDemo data with multiple rows for a given FruitName) to the one side (BenchmarkData has only one row for each FruitName). That's why PowerPivot sets the relationship direction that way.
And that's why you're getting the "Relationship Needed" results in the pivot. You've got fields from the "many" table on your Rows axis, and you're trying to Sum values from your "one" table. The natural filtering goes the other direction (from one to many), so typically you would slice by the fields in the "one" table, and sum up fields from the "many" table.
Hopefully that helps. Ultimately, there are better ways to model the data you're working with (dimensionally), but if you're just getting started, probably the easiest way for you to slice the data up the way you're trying to would be to flatten the DaysToExpiration for each fruit into the VendorDemo table.
You can easily accomplish this by creating a calculated column in your VendorDemo table from within the PowerPivot window. This new column will essentially look up the related FruitName and desired column from the Benchmark table for each VendorDemo row. Here's the DAX:
=RELATED(BenchmarkData[Days to Expiration])
Should look like this from within the PowerPivot window:
With that, you can add a calculated measure from the pivot table using just fields from the VendorDemo table. And I'd suggest in this case using Max or Avg instead of Sum (in case you have data from multiple Fruits under a given Vendor).
Here's a link to the example workbook I put together for your reference.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //