Forum Discussion
Sum By Row
Hello!
Need help with the correct approach to do the following. I have a table that looks like:
| Store | State | Product | Sales |
| A | CA | Apple | 5 |
| A | CA | Pear | 1 |
| A | CA | Orange | 1 |
| A | CA | Celery | 5 |
| B | CA | Apple | 5 |
| B | CA | Pear | 5 |
| B | CA | Orange | 5 |
| B | CA | Celery | 1 |
| C | TX | Apple | 5 |
| C | TX | Pear | 5 |
| C | TX | Orange | 4 |
| C | TX | Celery | 1 |
I'd like to add rows with the sum of the fruits:
| A | CA | Fruit | 7 |
| B | CA | Fruit | 15 |
| C | TX | Fruit | 14 |
Hi shuberml
Create a table with category and product as below, then relate two tables with "product" column,
Add columns into matrix and select "sum" aggregation, set filters as below:
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
shuberml , You need to define a category fruit / not fruit, and then it a simple sum.
This can be calculated column
Type = if([Product]<>"Celery","Fruit","Not Fruit")
Then you can filter on Fruit.
Or you can try SEGMENTATION
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationHi shuberml
Create a table with category and product as below, then relate two tables with "product" column,
Add columns into matrix and select "sum" aggregation, set filters as below:
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you both! Used the second solution, but both were effective!
