Forum Discussion
Link Column Names Wwith Another Table Rows
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!
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.
17 Replies
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.
Hi, Anonymous
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.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.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.
