Finding Sum of Minimum Values from Multiple Groups

[BugmanJ]

Good Day All,

In this example, I have:

• Baker Name
• Type of Food
• For Each Baker, an ingredient list with ingredient code and price. There are several similar ingrediants, each with a different code and price which could fit into the below ingredient groups
• A code matrix. For instance, Selecting from food "Tray Bake" means there are two ingredient groups

What I want:

• Upon selecting the food, PowerBi should give a total cost for each baker for that food using the cheapest ingriedant in that group.

As an example, if we select "Tray Bake", then from the food Matrix:

Desciption General	Food	Ingriendant	Code 1	Code 2	Code 3	Code 4
Milk	Tray Bake	Ingrediant 1	45H	19G	7FG	CM5
Margarine	Tray Bake	Ingrediant 2	2F	35L

Now just looking at a single Baker:

Ingredient Code	Description	Cost	Baker
45H	Cows Milk	5.10	Jsho
35L	Margarine (Dairy Free)	6.00	Jsho
19G	Milk (Lactose Free)	1.10	Jsho
7FG	Goats Milk	1.50	Jsho
2F	Margarine (Creamy)	4.80	Jsho
CM5	Chocolate Milk	1.25	Jsho

Now the minimum for each ingrediant:

Desciption General	Food	Ingriendant	Cost
Milk	Tray Bake	Ingrediant 1	1.10
Margarine	Tray Bake	Ingrediant 2	4.80

Which should then just give us in the final table

Baker	Food	Cost
JSHO	Tray Bake	5.90

Some bakers can not do all the ingriedants and should therefore be excluded for that selection.

There is also in there discount and region but thats for another day.

Can this be done in PowerBI? I have it working beautfilly in Tableau but have been asked to look at it for Powerbi. In reality there is several thousand lines, but the above is a good example using dummy data.

Link to dummy data: https://we.tl/t-Ffi8e1Y4y1 

Thank you

J

[BugmanJ]

Hi @v-yanjiang-msft ,

Thanks, but there is a problem. In my original request, i noted that bakers whom could not provide all the ingriendants should not end up in the final table, but in your example, bakers whom are not providing all the ingriendants are still ending up in the table.

Do you have a solution for this otherwise your work is spot on (for the calculation)

Thanks

[v-yanjiang-msft]

Hi @BugmanJ ,

According to your description, here's my solution.

1.In Power BI, the data model is very important for computing, one table per baker in your sample, it should append in Power Query. Select Append Queries>Append Queries as New.

Add all baker tables and select OK, the new table is renamed "Ingredient" in my sample.

2.Create a new table.

Table = GENERATE('Code Matrix',VALUES('Bakers'[Bakers]))

Make relationship like this:

3. Create a calculated column in the new table.

Cost =
MINX (
    FILTER (
        ALL ( 'Ingredient' ),
        'Ingredient'[Baker] = EARLIER ( 'Table'[Bakers] )
            && 'Ingredient'[Ingredient Code]
            IN { 'Table'[Code 1], 'Table'[Code 2], 'Table'[Code 3], 'Table'[Code 4] }
    ),
    'Ingredient'[Cost]
)

Get the correct result.

Another method also can get the correct answer at the ingriendant level without create new calculated table.

Create a measure.

Cost =
MINX (
    FILTER (
        ALL ( 'Ingredient' ),
        'Ingredient'[Baker] = MAX ( 'Bakers'[Bakers] )
            && 'Ingredient'[Ingredient Code]
                IN {
                    MAX ( 'Code Matrix'[Code 1] ),
                    MAX ( 'Code Matrix'[Code 2] ),
                    MAX ( 'Code Matrix'[Code 3] ),
                    MAX ( 'Code Matrix'[Code 4] )
                }
    ),
    'Ingredient'[Cost]
)

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.