Link Column Names Wwith Another Table Rows

[BobbyTheBuilder]

Hi,

I'm trying to do subtotals of specific column based on if their names are grouped together as stated in another table.

This would be the table where there would be subtotals (the ??).

Name	Tomatoes	Onions	Mushrooms	Beef	Ramen	Pork	Cilantro	Spaghetti	Ramen
Sabrina	57  $	26  $	98  $	13  $	95  $	4  $	57  $	??	??
Johnny	49  $	52  $	5  $	32  $	36  $	93  $	30  $	??	??
Philip	52  $	48  $	58  $	87  $	19  $	90  $	89  $	??	??
Nancy	46  $	56  $	25  $	44  $	92  $	56  $	69  $	??	??

This would be the table where the column names are grouped:

Category	Recipe
Tomatoes	Spaghetti
Onions	Spaghetti
Mushrooms	Spaghetti
Beef	Spaghetti
Ramen	Ramen
Pork	Ramen
Cilantro	Ramen

Thanks!

[BobbyTheBuilder]

My two tables are the two above. One table has the list of the people, and the other table has the categories. What I want to do is really to sum up the people spending based on if the columns titles are fit in the category.

[Syndicate_Admin · Accepted solution]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirements, share the expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Pleasecheck my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you don't meet your requirements, share your expected result.

Best regards
Community Support Team _ Eason
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, @BobbyTheBuilder

I suggest you create a new table in the advanced editor by following these steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Please check my attached pbix for more details)

The result will be shown below:

If there are not many categories, you can also try measures as shown below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If you do not meet your requirement, share your expected result.

Best regards
Community Support Team _ Eason
If this post helps,then consider Accepting it as the solution to help other members find it faster.

[v-easonf-msft]

Hi, @BobbyTheBuilder 

I would suggest you to create a new table in the advanced editor, as follow steps:

Duplicate a new table->pivot table->merge table->group by->merge table. (Please check my attached pbix for more details)

The result  will show as below:

If there are not many categories , you can also try measures  as below:

Measure_Ramen = SUM('Table'[Ramen])+SUM('Table'[Pork])+SUM('Table'[Cilantro])

Measure_Spaghetti = sum('Table'[Tomatoes])+Sum('Table'[Onions])+sum('Table'[Mushrooms])+sum('Table'[Beef])

If it doesn't meet your requirement ,please share your expected result.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[emilymgoitia]

Hey there.  What does your data look like?  Is it more like the first example or the second?  If the second, you can do a nested if statement that is something like if(containsstring,category,"ramen", "ramen", if(containsstring,category,"pork", "ramen", if(containsstring,category,"cilantro", "ramen", "Spaghetti") and then you can subtotal your recipes.  Let me know if you need more help with this.