PowerBI creating calculated measure for 1 product to be used with actual value of another product

Question

Problem: I have data from a pizza production plant containing product name, number of units produced and the time in hours. The product variable includes the final pizza and the raw material required for it. I am not able to calculate a weighted UPH measure, which would be dynamic based on the time frame selected and able to roll up the hierarchy explained below.

+-----------+----------------+----------------+
|   Pizza   | Units produced | Hours required |
+-----------+----------------+----------------+
| Pizza 1   | 23,156         | 12,154         |
| Pizza 2   | 25,145         | 21,154         |
| Pizza 3   | 2,055          | 2,015          |
| Pepperoni | 50,356         | 13,051         |
| Bacon     | 35,465         | 9,004          |
| Pineapple | 6,254          | 504            |
| Pizza 4   | 26,413         | 15,014         |
+-----------+----------------+----------------+

Explanation: I need to calculate the Units per hour (UPH) performance of each Pizza which is Units/Hours. However, I want to factor in the time in took to produce the raw materials for the Pizza so a simple calculation using the above hours is not useful for me. To get the weighted hours required to create a pizza, I have to calculate the hours required per unit (Hours per unit) = hours/unit, for the raw material and multiply it by the # of units for the final product (1 unit of raw material is used in 1 final product)

+-----------+----------------+----------------+----------------+
|   Pizza   | Units produced | Hours required | Hours per unit |
+-----------+----------------+----------------+----------------+
| Pizza 1   |  23,156        |  12,154        | 0.524874762    |
| Pizza 2   |  25,145        |  21,154        | 0.841280573    |
| Pizza 3   |  2,055         |  2,015         | 0.98053528     |
| Pepperoni |  50,356        |  13,051        | 0.259174676    |
| Bacon     |  35,465        |  9,004         | 0.253884111    |
| Pineapple |  6,254         |  504           | 0.080588423    |
| Pizza 4   |  26,413        |  15,014        | 0.568432211    |
+-----------+----------------+----------------+----------------+

Eg, for Pizza 1, Pepperoni and Pineapple are the raw materials used.

So I take the HPU of Pepperoni and Pineapple, multiple it by units of pizza 1 and add it to the hours required for pizza 1.

Weighted hours Pizza 1 = 12,154 + (0.259174676 * 23,156) + (0.080588423 * 23,156) I use this calculated Weighted hours to get the UPH.

+---------+--------------+----------+----------------+-------------+
| Product | Ingredients  |  Units   | Weighted hours |     UPH     |
+---------+--------------+----------+----------------+-------------+
| Pizza 1 |              |  23,156  |  20,021.55     | 1.156553563 |
| Pizza 1 | Pepperoni    |          |                |             |
| Pizza 1 | Pineapple    |          |                |             |
| Pizza 2 |              |  25,145  |  33,020.50     | 0.761496713 |
| Pizza 2 | Pepperoni    |          |                |             |
| Pizza 2 | Bacon        |          |                |             |
| Pizza 3 |              |  2,055   |  3,234.95      | 0.635250364 |
| Pizza 3 | Pepperoni    |          |                |             |
| Pizza 3 | Bacon        |          |                |             |
| Pizza 3 | Pineapple    |          |                |             |
| Pizza 4 |              |  26,413  |  15,014        | 1.759224724 |
+---------+--------------+----------+----------------+-------------+

I further want to roll up the UPH calculations to Pizza combos where Combo 1 = Pizza 1 + Pizza 2

And its weighted UPH = (Units of Pizza 1 + Pizza 2) / (Weighted Hours of Pizza 1 + Pizza 2)

+---------+---------+--------+-----------+-------------+
|  Combo  |         | Units  |   Hours   |     UPH     |
+---------+---------+--------+-----------+-------------+
| Combo 1 |         | 48,301 | 53,042    | 0.910617118 |
| Combo 1 | Pizza 1 | 23,156 | 20,021.55 |             |
| Combo 1 | Pizza 2 | 25,145 | 33,020.50 |             |
| Combo 2 |         | 27,200 | 36,255    | 0.750232198 |
| Combo 2 | Pizza 2 | 25,145 | 33,020.50 |             |
| Combo 2 | Pizza 3 | 2,055  | 3,234.95  |             |
+---------+---------+--------+-----------+-------------+

Further, this is how I plan to manage and bring in the pizza/ingredients/combo combination via excel into the model

+---------+---------+-------------+
|  Combo  |  Pizza  | Ingredients |
+---------+---------+-------------+
| Combo 1 | Pizza 1 |             |
| Combo 1 | Pizza 1 | Pepperoni   |
| Combo 1 | Pizza 1 | Pineapple   |
| Combo 1 | Pizza 2 |             |
| Combo 1 | Pizza 2 | Pepperoni   |
| Combo 1 | Pizza 2 | Bacon       |
| Combo 2 | Pizza 2 |             |
| Combo 2 | Pizza 2 | Pepperoni   |
| Combo 2 | Pizza 2 | Bacon       |
| Combo 2 | Pizza 3 |             |
| Combo 2 | Pizza 3 | Pepperoni   |
| Combo 2 | Pizza 3 | Bacon       |
| Combo 2 | Pizza 3 | Pineapple   |
| NA      | Pizza 4 |             |
+---------+---------+-------------+

I need help calculating the measure and/or suggestions of an approach I should take to get the Weighted UPH which can be used over time and be able to roll up as well.

Answer 1

I have an approach suggestion for you.

If I was going to do this in PBI, I would have an ingredients table, a pizza table and a combo table. (PBI calls a table a query)

Ingredients:

IngredientName   HourlyCost_PerUnit
Pepperoni        0.259174676

Pizza:

PizzaName  HPU           HPU_Extended               Ingredient1  Ingredient2   Units_Sold
Pizza1     0.524874762   (sum ingredients HPU)      Pepperoni    Bacon         25793

Combo:

ComboName   CombinedItem1   CombinedItem2   Units_Sold   HPU              HPU_Extended
Combo1      Pizza1          Pizza2          15268        (sum Pizza HPU)  (sum pizza HPU extended)

You would need to expand the count of columns in Pizza and Combo to suit your needs. As I happen to work in the Pizza industry as a second job, I am making the assumption these should be a manageable count of columns based on what we do.

I would then ensure I specify the relationships to work from Combo to Pizza to Ingredient. I would specify 'both' for the direction of filtering. In this approach, I envisage extended reporting limitations when using the 'single' setting. For instance, if you wanted to look at pizzas that only contain Pepperoni, when you try to filter it down by clicking pepperoni it wouldn't work.

You should then find it a lot easier to do calculated queries/columns.

Ref your intended excel input file. If you could break it down to how I have specified (table = sheet), it would work better, however, if you have to use that format, PBI can handle it by using 'Transform Data' where you could create tables by using the built-in tools.

A very strong recommendation to have a read on creating tables from your data in PBI as there are many pitfalls.

A perfect example of how it can be done is here.

Now your data should be granular enough to be dynamic within PBI.

Many Thanks POC