Power BI: Opportunity model (relating 3 tables)

2
votes

I'm trying to create an "opportunity" calculation model, but the output results that I'm getting are not accurate. Sample problem would be McDonald's and Burger King who sell food in various regions, some regions have both BK and McD in the area and they both sell similar food types, but some have both in the area however they can't fulfill the same order type (an example would be zip code 10049 where BK and McD both exist, but McD sells burgers and BK sells salads; so BK can cover the area, but can't fulfill potential customer want.)

In the example spreadsheet, there are three tabs, first with McD sales, second with BK sales, and the third reconciles the naming convention between McD's and BK's orders.

I started by connecting the tables with relationships. I figured I need to connect McD to BK by Zip, then McD to Crossreference. Due to many-to-many relationship limitations in PBI, I'm forced to create lookup tables with unique values for zips, and order names. Looks a bit messy, but does the job. The problem is that I can look up the zip code connections, but not the sales for the potential orders.

Relationships: enter image description here

This is a clear example of how things don't work. Zip code 10048 sums up McD's sales and displays it for each BK order type. The expected output would be $5 for angus and $3 for onion soup, $8 in total. enter image description here

If I try to connect crossreference BK order names to BK orders, then I get an ambiguity error. enter image description here

Spreadsheet data file: https://docs.google.com/spreadsheets/d/1WM9OD7voApax7uNJ6_bJk75zfj9FQN9tSf2jU1hXl7c/edit?usp=sharing

Excel and Power BI files: https://drive.google.com/drive/folders/1hOdP5ZglHcqo_dk2GMlXr6Xmc5Ywm6nj?usp=sharing

1
powerbidax
Do you need to have both "angus" and "cheesburger" related to "burger"? You don't appear to use "cheeseburger". – Alexis Olson
Yes, this example is a simplified variant of a much bigger dataset. The actual data/problem has over 100K records, and the mapping table has service types for two companies, where one company's service is more general, so the same type is shown multiple times. – haosmark

1 Answers

0
votes

I don't think you'll be able to do this exactly how you want to. It would basically equivalent to creating multiple relationships between two tables. Power BI doesn't let you do that.

There are some workarounds though. For example, you can pull over the McD[order] values into your BK table using a calculated column:

MDorder = MAXX(FILTER(Crossreference, Crossreference[BK] = BK[order]), Crossreference[McD])

This will allow you to pull across the price from the McD table using a lookup or similar max type function:

Price = LOOKUPVALUE(McD[price], McD[order], BK[McD Order], McD[Zip], BK[Zip])

or 

Price = MAXX(FILTER(McD, McD[Zip] = BK[Zip] && McD[order] = BK[McD Order]), McD[price])

Once you do that, you can work entirely on the BK table.

Note that some price rows will have nulls since there was no corresponding McD row with matching zip and order. (I suppose you could take the median price of those orders over the zip codes they do exist in and plug that in those cases...) If the price is uniform across zip codes, then this can be made simpler.

Also, notice that when you put the price into a table and use an implicit measure on it, it will likely default to a sum and you'll get $6 for 10048 and angus since you have duplicate rows. Switching to max will get you the $3 if that's what you prefer.

This type of merging is also possible to do in the query editor, but I couldn't play with that on the pbix you included since I don't have access to the data source on your C: drive.