Forum Discussion
Addition, sum logic in Power BI
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!!
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
1 Reply
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