Sum By Row

[shuberml]

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

[shuberml]

Thank you both! Used the second solution, but both were effective!

[v-juanli-msft · Accepted solution]

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.

[amitchandak]

@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-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization