Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |