Addition, sum logic in Power BI

[Worldbreaker]

I am having issues with how Power BI sums up my data.

basically, here is an example of my data:

Fruits	Basket 1	Basket 2	Basket 3	total Fruit	Additional Fruits on basket
Apple				1
Apple		Peach		1	1
Apple				1
Orange	Apple			1	1
Orange		Apple		1	1
Orange			Apple	1	1
Orange				1
Peach	Orange			1	1
Peach				1

and this is the intended visualization:

Fruits	Total Fruits	Total Additonal
Apple	3	3
Orange	4	1
Peach	2	1

instead what I get is:

Fruits	Total Fruits	Total Additonal
Apple	3	1
Orange	4	3
Peach	2	1

*in the total additional, its summing up everything that appears in all basket for oranges, even if it's for Apples and not Oranges

how can I get to the intended output?

Thanks!!

[v-huizhn-msft · Accepted solution]

Hi @Worldbreaker,

I try to reproduce and get the desired result, please review the following steps.

1. Click "New Table" under Modeling on Home page, type the following formula.

Table = UNION(UNION(SELECTCOLUMNS(Table1,"Basket",Table1[Basket 1]),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 2])),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 3]))

2. Create another new table based on the table above.

Table 2 = SUMMARIZE(FILTER('Table','Table'[Basket]<>BLANK()),'Table'[Basket],"Additional",COUNTA('Table'[Basket]))

3. Create a relationship between your table, and the new 'Table 2'.



4. Create a table visual, select the sum of total fruit, the 'Table 2'[Additional] but don't summarize, you will get the right result.



Thanks,
Angelia